Postgres: Coalesce single value from JSONB array of objects

Question

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?

Johnny · Accepted Answer · 2020-09-09 16:06:37Z

2

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

edited Sep 9, 2020 at 16:06

answered Sep 9, 2020 at 15:59

Johnny

3562 silver badges11 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Richard Over a year ago

You're a lifesaver @Johnny! This is exactly right. I ended up not needing the coalesce statement since we return only one result (and I don't care if I get null values). Thanks a million!

DanielV Over a year ago

I was looking for an option to filter null values and this address the issue

Mike Organek · Accepted Answer · 2020-09-09 15:54:31Z

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

Collectives™ on Stack Overflow

Postgres: Coalesce single value from JSONB array of objects

2 Answers 2

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related