CODEWITHSUNDEEP
sqlpostgresqljsonb
Pranay Soni
Pranay Soni

Reputation: 1066

select empty object in jsonb_each in postgres

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

Upvotes: 3

Views: 823

Answers (1)

Laurenz Albe
Laurenz Albe

Reputation: 246318

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.

Upvotes: 4

Related Questions