我正在尝试测试jsonPostgreSQL 9.3 中的类型。 我有一个json名为data的表中的列reports。JSON 看起来像这样:
json
data
reports
{ "objects": [ {"src":"foo.png"}, {"src":"bar.png"} ], "background":"background.png" }
我想在表中查询与“objects”数组中的“src”值匹配的所有报告。例如,是否可以在数据库中查询所有匹配的报告'src' = 'foo.png'?我成功编写了一个可以匹配的查询"background":
'src' = 'foo.png'
"background"
SELECT data AS data FROM reports where data->>'background' = 'background.png'
但由于"objects"有一系列值,我似乎无法写出有效的东西。是否可以在数据库中查询所有匹配的报告'src' = 'foo.png'?我已经查看了这些来源,但仍然无法得到它:
"objects"
我也尝试过这样的事情,但无济于事:
SELECT json_array_elements(data->'objects') AS data from reports WHERE data->>'src' = 'foo.png';
我不是 SQL 专家,所以我不知道我做错了什么。
jsonb
您 可以 使用与下面相同的查询,只需使用jsonb_array_elements().
jsonb_array_elements()
而是将jsonb“包含”运算符@>与表达式上的匹配 GIN 索引结合使用data->'objects':
@>
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"}]';
由于 keyobjects包含一个 JSON 数组 ,我们需要匹配搜索词中的结构并将数组元素也包含在方括号中。搜索普通记录时删除数组括号。
objects
更多解释和选项:
将 JSON 数组与子句json_array_elements()中的横向连接中的函数取消嵌套FROM并测试其元素:
json_array_elements()
FROM
SELECT data::text, obj FROM reports r, **json_array_elements(r.data# >'{objects}') obj** WHERE obj->>'src' = 'foo.png';
db <>fiddle here 旧sqlfiddle
或者,仅相当于 一层 嵌套:
SELECT * FROM reports r, **json_array_elements(r.data- >'objects') obj** WHERE obj->>'src' = 'foo.png';
->>,操作->符#>在手册中有说明。
->>
->
#>
两个查询都使用隐式JOIN LATERAL.
JOIN LATERAL