select empty object in jsonb_each in postgres

Question

How to select a empty object Record with jsonb_each function. because I select some extra field with jsonb_each key and value. but when a all record jsonb column in empty result is empty.

    create table newtest (id  SERIAL PRIMARY KEY,foo jsonb);

    insert into newtest (foo) values ('{"a":1, "c":2}'), ('{"b":1}'), ('{}');

    select * from newtest
    ID | foo      
    -----+----------------
     1 |  "{"a": 1, "c": 2}"
     2 |  "{"b": 1}"
     3 |  "{}"

    select id,(jsonb_each(foo)).key AS KEY, (jsonb_each(foo)).value AS value from newtest

    Result 
    ID | key | value      
    -----+----------------
     1 |  a  | 1
     1 |  c  | 2
     2 |  b  | 1

I need a result like

    ID | key | value      
    -----+----------------
     1 |  a  | 1
     1 |  c  | 2
     2 |  b  | 1
     3 |null | null

Maybe You will have to use your query on the right side of LEFT JOIN. — Roman Hocke
– Roman Hocke, Commented Oct 30, 2017 at 8:50

Laurenz Albe · Accepted Answer · 2017-10-30 09:03:57Z

4

A lateral left outer join should be the right thing:

SELECT newtest.id, item.key, item.value
FROM newtest
   LEFT JOIN LATERAL jsonb_each(newtest.foo) item ON TRUE;

 id | key | value 
----+-----+-------
  1 | a   | 1
  1 | c   | 2
  2 | b   | 1
  3 |     | 
(4 rows)

This will supply a NULL for missing entries on the right side.

answered Oct 30, 2017 at 9:03

Laurenz Albe

257k22 gold badges312 silver badges388 bronze badges

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

Collectives™ on Stack Overflow

select empty object in jsonb_each in postgres

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related