在Postgres中查询对象的JSON数组 [英] Querying a JSON array of objects in Postgres

问题描述

我有一个带json数据字段的postgres数据库。
我拥有的json是一个对象数组：

I have a postgres db with a json data field. The json I have is an array of objects:

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

我试图返回JSON数组中特定键的值，因此在上面的示例中，我想返回name的值。

I'm trying to return values for a specific key in a JSON array, so in the above example I'd like to return the values for name.

何时我使用以下查询，我只得到返回的NULL值：

When I use the following query I just get a NULL value returned:

SELECT data->'name' AS name FROM json_test

我假设这是因为它是对象数组吗？

Im assuming this is because it's an array of objects? Is it possible to directly address the name key?

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

Ultimately what I need to do is to return a count of every unique name, is this possible?

谢谢！

推荐答案

您必须取消嵌套首先使用函数（ json_array_elements 或 jsonb_array_elements 如果您具有jsonb数据类型），然后可以通过指定键来访问这些值。

you have to unnest the array of json-objects first using the function (json_array_elements or jsonb_array_elements if you have jsonb data type), then you can access the values by specifying the key.

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"

要获得唯一名称的计数，它与上面的查询类似，不同之处在于对 yx应用了countdistinct聚合函数-- >>名称

To get a count of unique names, its a similar query to the above, except the count distinct aggregate function is applied to 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，不支持相等比较。

It is necessary to use ->> instead of -> as the former (->>) casts the extracted value as text, which supports equality comparison (needed for distinct count), whereas the latter (->) extracts the value as json, which does not support equality comparison.

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

Alternatively, convert the json as jsonb and use jsonb_array_elements. JSONB supports the equality comparison, thus it is possible to use COUNT DISTINCT along with extraction via ->, i.e.

COUNT(DISTINCT (y.x::jsonb)->'name')

这篇关于在Postgres中查询对象的JSON数组的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！