Get key and value from unstructured JSON data

Question

I have the following table with some data:

Table:

create table tbl_jsontest
(
   id int,
   jdata json 
);

Records:

insert into tbl_jsontest values(1,'{"Id":1,"Name":"Jack"}');
insert into tbl_jsontest values(1,'[{"Id":2,"Name":"Mak"},{"Name":"Aez","Address":"ZX"}]');
insert into tbl_jsontest values(1,'[{"Id":5,"Name":"Lee"}]');

Query:

SELECT json_data.key AS key1,
       json_data.value AS value1
FROM tbl_jsontest, 
json_each_text(tbl_jsontest.jdata) AS json_data;

Getting an error:

ERROR: cannot deconstruct an array as an object

Answer 1

The json_each_text function takes a json object as input and does not work with array.

Documentation https://www.postgresql.org/docs/current/functions-json.html

json_each_text ( json ) → setof record ( key text, value text ) Expands the top-level JSON object into a set of key/value pairs. The returned values will be of type text.

You can first expand the arrays with json_array_elements and then expand the objects

Also you should reformat jdata to same format

Records:

insert into tbl_jsontest values(1,'[{"Id":1,"Name":"Jack"}]');
insert into tbl_jsontest values(1,'[{"Id":2,"Name":"Mak"},{"Name":"Aez","Address":"ZX"}]');
insert into tbl_jsontest values(1,'[{"Id":5,"Name":"Lee"}]');

Query:

select t.id, key, value
from (SELECT id, json_array_elements(tbl_jsontest.jdata) d
      FROM tbl_jsontest) t,
     json_each_text(t.d);

Results:

+--+-------+-----+
|id|key    |value|
+--+-------+-----+
|1 |Id     |2    |
|1 |Name   |Mak  |
|1 |Name   |Aez  |
|1 |Address|ZX   |
|1 |Id     |5    |
|1 |Name   |Lee  |
|1 |Id     |1    |
|1 |Name   |Jack |
+--+-------+-----+