如何将JSON数组数组转换为列和行

I'm pulling data from an API in JSON with a format like the example data below. Where essentially every "row" is an array of values. The API doc defines the columns and their types in advance. So I know the col1 is, for example, a varchar, and that col2 is an int.

我正在从JSON中的API中提取数据，其格式类似于下面的示例数据。基本上每个“行”都是一个值数组。 API文档提前定义列及其类型。所以我知道col1例如是一个varchar，而col2是一个int。

CREATE TEMP TABLE dat (data json);
INSERT INTO dat
VALUES ('{"COLUMNS":["col1","col2"],"DATA":[["a","1"],["b","2"]]}');

I want to transform this within PostgreSQL 9.3 such that I end up with:

我想在PostgreSQL 9.3中对此进行转换，以便我最终得到：

col1 | col2
------------
  a  |  1
  b  |  2

Using json_array_elements I can get to:

使用json_array_elements我可以：

SELECT json_array_elements(data->'DATA') 
FROM dat

json_array_elements
json
---------
["a","1"]
["b","2"]

but then I can't figure out how to do either convert the JSON array to a PostgreSQL array so I can perform something like unnest(ARRAY['a','1'])

但后来我无法弄清楚如何将JSON数组转换为PostgreSQL数组，这样我就能执行像remast这样的事情（ARRAY ['a'，'1']）

2 个解决方案

#1

General case for unknown columns

To get a result like

得到一个像这样的结果

col1 | col2
------------
  a  |  1
  b  |  2

will require a bunch of dynamic SQL, because you don't know the types of the columns in advance, nor the column names.

将需要一堆动态SQL，因为您事先不知道列的类型，也不知道列名。

You can unpack the json with something like:

您可以使用以下内容解压缩json：

SELECT
  json_array_element_text(colnames, colno) AS colname,
  json_array_element_text(colvalues, colno) AS colvalue,
  rn,
  idx,
  colno
FROM (
  SELECT
    data -> 'COLUMNS' AS colnames,
    d AS colvalues,
    rn,
    row_number() OVER () AS idx
  FROM (
    SELECT data, row_number() OVER () AS rn FROM dat
  ) numbered
  cross join json_array_elements(numbered.data -> 'DATA') d
) elements
cross join generate_series(0, json_array_length(colnames) - 1) colno;

producing a result set like:

生成如下结果集：

 colname | colvalue | rn | idx | colno 
---------+----------+----+-----+-------
 col1    | a        |  1 |   1 |     0
 col2    | 1        |  1 |   1 |     1
 col1    | b        |  1 |   2 |     0
 col2    | 2        |  1 |   2 |     1
(4 rows)

You can then use this as input to the crosstab function from the tablefunc module with something like:

然后，您可以使用此函数作为tablefunc模块中交叉表函数的输入，例如：

SELECT * FROM crosstab('
SELECT
  to_char(rn,''00000000'')||''_''||to_char(idx,''00000000'') AS rowid,
  json_array_element_text(colnames, colno) AS colname,
  json_array_element_text(colvalues, colno) AS colvalue
FROM (
  SELECT
    data -> ''COLUMNS'' AS colnames,
    d AS colvalues,
    rn,
    row_number() OVER () AS idx
  FROM (
    SELECT data, row_number() OVER () AS rn FROM dat
  ) numbered
  cross join json_array_elements(numbered.data -> ''DATA'') d
) elements
cross join generate_series(0, json_array_length(colnames) - 1) colno;
') results(rowid text, col1 text, col2 text);

producing:

生产：

        rowid        | col1 | col2 
---------------------+------+------
  00000001_ 00000001 | a    | 1
  00000001_ 00000002 | b    | 2
(2 rows)

The column names are not retained here.

这里不保留列名。

If you were on 9.4 you could avoid the row_number() calls and use WITH ORDINALITY, making it much cleaner.

如果您使用的是9.4，则可以避免使用row_number（）调用并使用WITH ORDINALITY，使其更清晰。

Simplified with fixed, known columns

Since you apparently know the number of columns and their types in advance the query can be considerably simplified.

由于您显然事先知道列数及其类型，因此可以大大简化查询。

SELECT
  col1, col2
FROM (
  SELECT
    rn,
    row_number() OVER () AS idx,
    elem ->> 0 AS col1,
    elem ->> 1 :: integer AS col2
  FROM (
    SELECT data, row_number() OVER () AS rn FROM dat
  ) numbered
  cross join json_array_elements(numbered.data -> 'DATA') elem
  ORDER BY 1, 2
) x;

result:

结果：

 col1 | col2 
------+------
 a    |    1
 b    |    2
(2 rows)

Using 9.4 WITH ORDINALITY

If you were using 9.4 you could keep it cleaner using WITH ORDINALITY:

如果您使用的是9.4，则可以使用WITH ORDINALITY保持清洁：

SELECT
  col1, col2
FROM (
  SELECT
    elem ->> 0 AS col1,
    elem ->> 1 :: integer AS col2
  FROM
    dat
  CROSS JOIN
    json_array_elements(dat.data -> 'DATA') WITH ORDINALITY AS elements(elem, idx)
  ORDER BY idx
) x;

#2

this code worked fine for me, maybe it be useful for someone.

这段代码对我来说很好，也许对某人有用。

select to_json(array_agg(t))
 from (
  select text, pronunciation,
   (
     select array_to_json(array_agg(row_to_json(d)))
    from (
      select part_of_speech, body
       from definitions
       where word_id=words.id
       order by position asc
     ) d
   ) as definitions
  from words
  where text = 'autumn'
) t

Credits: https://hashrocket.com/blog/posts/faster-json-generation-with-postgresql

致谢：https：//hashrocket.com/blog/posts/faster-json-generation-with-postgresql