Json column with array to record

Question

I need to write SQL code, that INSERT into some table data, that stored in another table as JSON. PostgreSQL 9.5.

I have a table named comments. It has a JSON colum refs with data like this:

[{"author":"John","tags":["ruby","rails"]}, {"author":"Nick","tags":["sql"]}]

As you can see, there maybe more than one item (pair) in the JSON.

I need to write SQL code, that will take all the records from comments where refs IS NOT NULL and INSERT INTO comments_refs (dont ask why am I need it :) ), that looks like:

id                | integer                | not null default nextval(...)
comment_id        | integer                | not null
author            | character varying(255) | not null
tags              | text[]                 | not null default '{}'::text[]

I try to play with json_to_recordset, but it doesn't work with arrays (see http://postgresql.nabble.com/bug-in-json-to-record-with-arrays-td5828415.html ). Next, I try something like:

SELECT json_array_elements(rec.refs) FROM comments AS rec;

but I do not come up with how to do it .. Maybe somebody can help me. Thanks.

klin · Accepted Answer · 2016-02-09 11:43:36Z

1

Use json_array_elements():

select comment_id, author, array_agg(tag) tags
from (
    select comment_id, e->>'author' author, e->'tags' tags
    from comments, json_array_elements(refs) e
    ) s,
    json_array_elements_text(tags) tag
group by 1, 2;

 comment_id | author |     tags     
------------+--------+--------------
          1 | John   | {ruby,rails}
          1 | Nick   | {sql}
(2 rows)

edited Feb 9, 2016 at 11:43

answered Feb 9, 2016 at 10:21

klin

123k15 gold badges240 silver badges262 bronze badges

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

2 Comments

Jack Owels Over a year ago

What is ref there?

klin Over a year ago

The json column; should be refs.

Collectives™ on Stack Overflow

Json column with array to record

1 Answer 1

2 Comments

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Related