PostgreSQL 9.5 : Display json data into table

Question

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

You can go through postgresql.org/docs/9.5/static/functions-json.html and use the functions accordingly. — Pragun
– Pragun, Commented Nov 1, 2016 at 5:04

klin · Accepted Answer · 2016-11-01 05:05:18Z

3

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)

answered Nov 1, 2016 at 5:05

klin

123k15 gold badges240 silver badges262 bronze badges

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

Collectives™ on Stack Overflow

PostgreSQL 9.5 : Display json data into table

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