Menu
How can I coalesce a null column into an empty JSONB array? This doesn't work:
SELECT jsonb_array_elements(coalesce(null_column, '{}'::jsonb))
FROM table
WHERE id = 13;
-- ERROR: cannot extract elements from an object
Neither this:
SELECT jsonb_array_elements(coalesce(null_column, '[]'::jsonb))
FROM table
WHERE id = 13;
-- ERROR: cannot extract elements from a scalar
958
jsonb[] is not an "extra" datatype, it's simply an array of JSONB values. Similar to text[] or integer[] . You can create arrays from every type.
Postgres offers a jsonb_set function for updating JSON fields. The second parameter path defines, which property you want to update. To update items in an array, you can use an index-based approach. To update the first entry in the items array in the example above, a path woud look like this: {items, 0, customerId} .
Null is entered into the database when the value passed into JSONB. valueOf is null. The behavior is also a bit confusing since JSONB is just a simple wrapper so one cannot see why would it not handle null well.
Querying the JSON document PostgreSQL has two native operators -> and ->> to query JSON documents. The first operator -> returns a JSON object, while the operator ->> returns text. These operators work on both JSON as well as JSONB columns. There are additional operators available for JSONB columns.
{} is an object but jsonb_array_elements expects an array, so replace {} with []
Make sure that both arguments return a jsonb array. For example, if your column is an integer, you can use concat to add the square brackets and ::jsonb for the conversion
SELECT jsonb_array_elements(coalesce(concat('[',my_column,']')::jsonb,'[]'::jsonb))
Here is SQL code for accomplishing what you want:
SELECT jsonb_array_elements(coalesce(null_column, '[{}]'::jsonb))
FROM table
WHERE id = 13;
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
