sql - Json列与要记录的数组

我需要编写SQL代码，插入到一些表数据中，作为JSON存储在另一个表中。PostgreSQL 9.5版。
我有一个名为comments的表。它有一个JSON列引用，数据如下：

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

如您所见，JSON中可能有多个项（对）。
我需要编写SQL代码，它将从引用不为空的注释中获取所有记录，并插入到注释中（不要问我为什么需要它：），如下所示：

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

我试着玩json_to_recordset，但它不适用于数组（请参见http://postgresql.nabble.com/bug-in-json-to-record-with-arrays-td5828415.html）。下一步，我试着这样做：

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

但我不知道怎么做。。也许有人能帮我。谢谢。

最佳答案

使用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)

json列

sql - Json列与要记录的数组