MAK MAK - 1 month ago 5
JSON Question

PostgreSQL 9.5 : Display json data into table

I have the following record in the table

table_json
:

Table:

id doc
---------------------------------------
1
{"name":"Shaw",
"address":{"line1":"L1",
"line2":"L2",
"zipcode":"12345"
}
}


Note: Column
doc
is of type
json
. Now i want to print the json data into the form of
the following one.

Expected output:

id name address
--------------------------
1 Shaw L1,L2,12345

Answer

Use json_each_text() in lateral join:

with a_table (id, doc) as (
values
(1, '{
    "name": "Shaw", 
    "address":{
        "line1":"L1",
        "line2":"L2",
        "zipcode":"12345"
        }
    }'::json)
)
select 
    id, 
    doc->>'name' as name, 
    string_agg(value, ',') as address
from a_table, 
lateral json_each_text(doc->'address')
group by 1, 2;

 id | name |   address   
----+------+-------------
  1 | Shaw | L1,L2,12345
(1 row)
Comments