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: stackoverflow.com/a/26514968/939860

Erwin Brandstetter
– Erwin Brandstetter

2019-04-08 00:11:16 +00:00
Commented Apr 8, 2019 at 0:11 — Erwin Brandstetter
– Erwin Brandstetter, Commented Apr 8, 2019 at 0:11

Bergi · Accepted Answer · 2019-09-27 18:01:27Z

1

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

answered Sep 27, 2019 at 18:01

Bergi

671k162 gold badges1k silver badges1.5k bronze badges

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

Comments

Kaushik Nayak · Accepted Answer · 2019-04-06 18:22:22Z

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 6, 2019 at 18:22

Kaushik Nayak

32k6 gold badges36 silver badges54 bronze badges

Collectives™ on Stack Overflow

jsonb_array_elements does not return row, when not found

2 Answers 2

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related