查询JSON类型内的数组元素 [英] Query for array elements inside JSON type

问题描述

我正在尝试测试PostgreSQL 9.3中的json类型.
我在名为reports的表中有一个名为data的json列. JSON看起来像这样:

{
  "objects": [
    {"src":"foo.png"},
    {"src":"bar.png"}
  ],
  "background":"background.png"
}

我想在表中查询与对象"数组中"src"值匹配的所有报告.例如，是否可以在数据库中查询与'src' = 'foo.png'匹配的所有报告?我成功编写了一个可以匹配"background":

的查询

SELECT data AS data FROM reports where data->>'background' = 'background.png'

但是由于"objects"具有值数组，所以我似乎无法编写出有效的东西.是否可以在数据库中查询与'src' = 'foo.png'匹配的所有报告?我查看了这些来源，但仍然无法了解:

• http://www.postgresql.org/docs/9.3/static/functions-json.html
• 如何使用新的PostgreSQL JSON数据类型中包含哪些字段?
• http://michael.otacoo .com/postgresql-2/postgres-9-3-feature-highlight-json-operators/

我也尝试过类似的方法，但无济于事:

SELECT json_array_elements(data->'objects') AS data from reports
WHERE  data->>'src' = 'foo.png';

我不是SQL专家，所以我不知道自己在做什么错.

解决方案

json在Postgres 9.3 +

中

使用功能 json_array_elements() 在FROM子句的横向联接中并测试其元素:

WITH reports(data) AS (
   VALUES ('{"objects":[{"src":"foo.png"}, {"src":"bar.png"}]
           , "background":"background.png"}'::json)
   ) 
SELECT *
FROM   reports r, json_array_elements(r.data#>'{objects}') obj
WHERE  obj->>'src' = 'foo.png';

CTE (WITH查询)只是替代用于表reports.
或者，仅相当于单个嵌套级别:

SELECT *
FROM   reports r, json_array_elements(r.data->'objects') obj
WHERE  obj->>'src' = 'foo.png';

->>，和#>运算符在手册中进行了说明.

两个查询都使用隐式 JOIN LATERAL . >

SQL小提琴.

密切相关的答案:

• 在JSON列中查询数组元素

Postgres 9.4及更高版本中的

jsonb

使用等效的 jsonb_array_elements() .

更好，但是，使用新的包含"运算符@>(最好与表达式data->'objects'上的匹配GIN索引结合使用):

CREATE INDEX reports_data_gin_idx ON reports
USING gin ((data->'objects') jsonb_path_ops);

SELECT * FROM reports WHERE data->'objects' @> '[{"src":"foo.png"}]';

由于键objects具有JSON array ，因此我们需要匹配搜索项中的结构并将数组元素也包装在方括号中.搜索纯记录时，请放下数组括号.

详细说明和更多选项:

• 在JSON数组

I'm trying to test out the json type in PostgreSQL 9.3.
I have a json column called data in a table called reports. The JSON looks something like this:

{
  "objects": [
    {"src":"foo.png"},
    {"src":"bar.png"}
  ],
  "background":"background.png"
}

I would like to query the table for all reports that match the 'src' value in the 'objects' array. For example, is it possible to query the DB for all reports that match 'src' = 'foo.png'? I successfully wrote a query that can match the "background":

SELECT data AS data FROM reports where data->>'background' = 'background.png'

But since "objects" has an array of values, I can't seem to write something that works. Is it possible to query the DB for all reports that match 'src' = 'foo.png'? I've looked through these sources but still can't get it:

• http://www.postgresql.org/docs/9.3/static/functions-json.html
• How do I query using fields inside the new PostgreSQL JSON datatype?
• http://michael.otacoo.com/postgresql-2/postgres-9-3-feature-highlight-json-operators/

I've also tried things like this but to no avail:

SELECT json_array_elements(data->'objects') AS data from reports
WHERE  data->>'src' = 'foo.png';

I'm not an SQL expert, so I don't know what I am doing wrong.

解决方案

json in Postgres 9.3+

Unnest the JSON array with the function json_array_elements() in a lateral join in the FROM clause and test for its elements:

WITH reports(data) AS (
   VALUES ('{"objects":[{"src":"foo.png"}, {"src":"bar.png"}]
           , "background":"background.png"}'::json)
   ) 
SELECT *
FROM   reports r, json_array_elements(r.data#>'{objects}') obj
WHERE  obj->>'src' = 'foo.png';

The CTE (WITH query) just substitutes for a table reports.
Or, equivalent for just a single level of nesting:

SELECT *
FROM   reports r, json_array_elements(r.data->'objects') obj
WHERE  obj->>'src' = 'foo.png';

->>, -> and #> operators are explained in the manual.

Both queries use an implicit JOIN LATERAL.

SQL Fiddle.

Closely related answer:

• Query for element of array in JSON column

jsonb in Postgres 9.4+

Use the equivalent jsonb_array_elements().

Better yet, use the new "contains" operator @> (best in combination with a matching GIN index on the expression data->'objects'):

CREATE INDEX reports_data_gin_idx ON reports
USING gin ((data->'objects') jsonb_path_ops);

SELECT * FROM reports WHERE data->'objects' @> '[{"src":"foo.png"}]';

Since the key objects holds a JSON array, we need to match the structure in the search term and wrap the array element into square brackets, too. Drop the array brackets when searching a plain record.

Detailed explanation and more options:

• Index for finding an element in a JSON array

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