jsonb_array_elements does not return row, when not found

Question

I use cross join to unnest nested json values, but when filed not found it returns no record. is there a solution for the below using left join somehow?

create table json_example as
select '{"id": "21397",  "fields": { "labels": [] , "subtasks": [{"id": "10217"}] }}'::jsonb as value
union all
select '{"id": "21397",  "fields": { "labels": [] ,  "subtasks" : []  }}'::jsonb

returns both rows:

select * from json_example

the below returns only one row:

select subtasks.value->>'id'  
  from json_example h cross join
       jsonb_array_elements(value->'fields'->'subtasks') as subtasks

how i can solve the above, problem to have both records?

May be of help: https://stackoverflow.com/a/26514968/939860 – Erwin Brandstetter Apr 08 '19 at 00:11 — Erwin Brandstetter, Apr 08 '19 at 00:11

score 1 · Answer 1 · answered Sep 27 '19 at 18:01

You don't need to use a cross join, a simple lateral query will do - you don't even need the LATERAL keyword when calling a table function:

select subtasks.value->>'id'  
  from json_example,
       jsonb_array_elements(value->'fields'->'subtasks') as subtasks

But you can also put it right inside your select expression:

select jsonb_array_elements(value->'fields'->'subtasks') ->> 'id'  
  from json_example

score 0 · Accepted Answer · answered Apr 06 '19 at 18:22

0

You may add on true for left join to work.

select subtasks->>'id'  
  from json_example h left join
       jsonb_array_elements(value->'fields'->'subtasks') as subtasks on true

answered Apr 06 '19 at 18:22

Kaushik Nayak

30,772
5
32
45

jsonb_array_elements does not return row, when not found

2 Answers2

Linked