CODEWITHSUNDEEP
postgresql
Richard
Richard

Reputation: 689

Postgres: Coalesce single value from JSONB array of objects

I am using a JSONB column in a subquery where I need to return a single value. Each row of the JSONB column has the following form:

jsonb_column
------------

{
  'data': [
    {'type': 1, 'other':'stuff'}
    {'other':'stuff'}
    {'type': 1, 'other':'stuff'}
  ],
  'other':'stuff'
}

I would like to return the first non-null value of jsonb_column -> data -> type, which would return 1 in this example.

I have tried unnesting the array elements:

SELECT jsonb_array_elements(jsonb_column -> 'data') ->> 'type'

This works, but it produces multiple type values. When I try to COALESCE to get a single value, I get an error:

SELECT COALESCE(jsonb_array_elements(jsonb_column -> 'data') ->> 'type')

ERROR: set-returning functions are not allowed in COALESCE
  Hint: You might be able to move the set-returning function into a LATERAL FROM item.

I'm not sure how to implement this hint, or whether it is even useful in this case. Am I on the right track with unnesting the array elements, or is there a better way to get the first field value from an array of jsonb objects?

Upvotes: 0

Views: 1955

Answers (2)

Johnny
Johnny

Reputation: 356

After expanding jsonb to rows, you can apply filtering (WHERE) and limitation (LIMIT) to obtain what you need.

select coalesce(col->>'type', 'all_fields_are_null')
from jsonb_array_elements('
    {
        "data": [
            {"type": 1, "other": "stuff"},
            {"other": "stuff"},
            {"type": 1, "other": "stuff"},
            {"type": 2, "other": "stuff"}
        ],
        "other": "stuff"
    }'::jsonb -> 'data'
) tb (col)
where col->>'type' is not null
limit 1

(This function returns rows) jsonb_array_elements(jsonb) - Expands a JSON array to a set of JSON values. https://www.postgresql.org/docs/12/functions-json.html

(This function requires arguments, not rows) coalesce(...) - The COALESCE function returns the first of its arguments that is not null. https://www.postgresql.org/docs/12/functions-conditional.html#FUNCTIONS-COALESCE-NVL-IFNULL

Upvotes: 2

Mike Organek
Mike Organek

Reputation: 12484

jsonb_array_elements() does turn a json array into rows. You cannot use coalesce() across multiple rows.

Assuming your table has an id column, this (substituting your table for the cte) should get what you seek.

with jdata as (
  select 1 as id, '{
  "data": [
    {"type": 1, "other":"stuff"},
    {"other":"stuff"},
    {"type": 1, "other":"stuff"}
  ],
  "other":"stuff"
}'::jsonb as jsonb_column
)
select distinct on (id) *, obj->>'type' as first_type
  from jdata
       cross join lateral 
         jsonb_array_elements(jsonb_column->'data')
           with ordinality as j(obj, rn)
 where obj->>'type' is not null
 order by id, rn;

-[ RECORD 1 ]+---------------------------------------------------------------------------------------------------------------
id           | 1
jsonb_column | {"data": [{"type": 1, "other": "stuff"}, {"other": "stuff"}, {"type": 1, "other": "stuff"}], "other": "stuff"}
obj          | {"type": 1, "other": "stuff"}
rn           | 1
first_type   | 1

Upvotes: 0

Related Questions