Postgresql 在 Postgres 中查询 JSON 对象数组

发布于07月10日

我有一个带有json数据字段的postgres db.

[{"name":"Mickey Mouse","age":10},{"name":"Donald Duck","age":5}]

我想在上面的示例中返回一个特定于JSON的值.

使用以下查询时，只返回一个空值:

SELECT data->'name' AS name FROM json_test

我猜这是因为它是一个对象数组？是否可以直接寻址名称密钥？

最终我需要做的是返回每个唯一名称的计数，这可能吗？

谢谢

推荐答案

您必须首先使用函数(json_array_elements or 101 if you have jsonb data type)访问json对象数组，然后可以通过指定键来访问这些值.

WITH json_test (col) AS (
  values (json '[{"name":"Mickey Mouse","age":10},{"name":"Donald Duck","age":5}]')
)
SELECT
  y.x->'name' "name"
FROM json_test jt, 
LATERAL (SELECT json_array_elements(jt.col) x) y

-- outputs:
name
--------------
"Mickey Mouse"
"Donald Duck"

要获得唯一名称的计数，它与上面的查询类似，只是count distinct aggregate函数应用于y.x->>name

WITH json_test (col) AS (
  values (json '[{"name":"Mickey Mouse","age":10},{"name":"Donald Duck","age":5}]')
)
SELECT
  COUNT( DISTINCT y.x->>'name') distinct_names
FROM json_test jt, 
LATERAL (SELECT json_array_elements(jt.col) x) y

有必要使用->>而不是->，因为前者(->>)将提取的值转换为文本，支持相等比较(用于不同计数)，而后者(->)将值提取为json，不支持相等比较.

或者，将json转换为jsonb，并使用jsonb_array_elements.JSONB支持相等比较，因此可以通过->使用COUNT DISTINCT和提取，即.