arrays - 如何将 PostgreSQL 中带有 JSON 数组字段的表解析为行？

Question

我有一个包含 json 数组的表。以下是该字段内容的示例:

SELECT json_array FROM table LIMIT 5;

结果:

[{"key1":"value1"}, {"key1":"value2"}, ..., {"key2":"value3"}]
[]
[]
[]{"key1":"value1"}
[]

如何检索所有值并计算找到的每个值的数量？

我正在使用 PostgreSQL 9.5.14，我已经尝试了这里的解决方案 Querying a JSON array of objects in Postgres以及我最后一个问题中另一位慷慨的 stackoverflow 用户向我建议的那些:How can I parse JSON arrays in postgresql?

我试过了:

SELECT 
    value -> 'key1'
FROM
    table,
    json_array_elements(json_array);

遗憾的是，由于收到错误，这对我不起作用:
无法在标量上调用 json_array_elements
This error happens when using a query that returns more than one row or more than one column as a scalar subquery.

我尝试的另一个解决方案是:

SELECT json_array as json, (json_array->0), 
  coalesce(
    case
      when (json_array->0) IS NULL then null
      else (json_array->0->>'key1')
    end,
  'No value') AS "Value"
FROM table;

它只返回“值”的空值

引用 Querying a JSON array of objects in Postgres我也尝试使用此解决方案:

WITH json_test (col) AS (
values (json_arrays)
)
SELECT
  y.x->'key1' "key1"
FROM json_test jt, 
LATERAL (SELECT json_array_elements(jt.col) x) y;

但我需要能够将 json_arrays 的所有元素放入 json_test

到目前为止，我只尝试列出所有 json 数组中的所有值，但我理想的查询最终结果类似于:

值(value) |数量
--------------
值 1 | 48
值2 | 112
值3 | 93
值4 | 0

再次感谢您对此提供的任何帮助，在此先感谢您。

Answer 1

step-by-step demo:db<>fiddle

SELECT
    each.value,
    COUNT(*)
FROM
    data,
    json_array_elements(json_array) elems,   -- 1
    json_each_text(elems) each               -- 2
GROUP BY each.value                          -- 3

1. 为每个数组元素将数组扩展为一行
2. 将键/值对分成两列
3. 按新值列/计数分组