如何过滤json列中嵌套值上的行? [英] How to filter rows on nested values in a json column?

问题描述

这是我的表(简体，只有重要的列):

Here is my table (simplified, only significant columns):

CREATE TABLE things (
  id serial primary key
, name varchar
, blueprint json default '{}'
);

以及一些示例数据:

# select * from things;

 id |  name   |                                  blueprint
----+---------+-----------------------------------------------------------------------------
  1 | Thing 1 | {}
  2 | Thing 2 | {"1":{"name":"Iskapola","wight":"2"}}
  3 | Thing 3 | {"1":{"name":"Azamund","weight":"3"}, "2":{"name":"Iskapola","weight":"1"}}
  4 | Thing 4 | {"1":{"name":"Ulamir","weight":"1"}, "2":{"name":"Azamund","weight":"1"}}

我想选择在name键下任意位置具有'Azamund'的行. 像这样:

I'd like to select rows that have 'Azamund' anywhere under the name key. Something like this:

# select * from things where * ->> 'name' = 'Azamund';

 id |      blueprint
----+----------------------------------------------------------------------------
  7 | {"1":{"name":"Azamund","weight":"3"}, "2":{"name":"Iskapola","weight":"1"}}
  8 | {"1":{"name":"Ulamir","weight":"1"}, "2":{"name":"Azamund","weight":"1"}}

数据完全像示例中一样嵌套(仅一层).
当前，我们正在使用PostgreSQL 9.3.5.

Data is nested exactly like in the sample (only one level).
Currently we are using PostgreSQL 9.3.5.

在PostgreSQL 9.3中可以吗?也许是9.4?

Is it possible in PostgreSQL 9.3? Maybe 9.4?

推荐答案

您的查询已关闭. json_each() 是关键功能.或jsonb_each()表示jsonb.进行了一些改进:

Your query is close. json_each() is the key function. Or jsonb_each() for jsonb. A couple of improvements:

SELECT *
FROM   things t
WHERE  EXISTS (
   SELECT FROM json_each(t.blueprint) b
   WHERE  b.value->>'name' ILIKE 'azamund'
   );

旧的 sqlfiddle
db<>小提琴此处

Old sqlfiddle
db<>fiddle here

• json_each()已将值作为json数据类型返回.不需要额外的演员表.

• json_each() already returns the value as json data type. No need for an additional cast.

更好的是，在EXISTS中使用LATERAL引用.这比使用SELECT列表中的set-returning函数取消嵌套要干净得多.相关:

Better, yet, use a LATERAL reference in EXISTS. This is much cleaner than unnesting with a set-returning function in the SELECT list. Related:

• 呼叫一组多次返回带有数组参数的函数

使用ILIKE(~~*)进行模式匹配.正则表达式匹配(~，~*)更强大，但也更昂贵.因此，请尽可能使用基本的LIKE/ILKE.详细信息:

Use ILIKE (~~*) for the pattern match. Regular expression matches (~, ~*) are more powerful, but also more expensive. So use the basic LIKE / ILKE where you can. Details:

• 与LIKE，SIMILAR TO或PostgreSQL中的正则表达式匹配的模式

您已经看到了有关JSON数组的相关答案:

You have already seen my related answer for JSON arrays:

• 如何使用新的PostgreSQL JSON数据类型中包含哪些字段?

虽然查询嵌套的JSON对象看起来很简单，但是数组具有更好的索引支持:

While the query for nested JSON objects seems just as simple, there is superior index support for the array:

• 在JSON数组

在Postgres 12中使用SQL/JSON可以变得更简单/更高效...

May get simpler / more efficient with SQL/JSON in Postgres 12 ...

这篇关于如何过滤json列中嵌套值上的行?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！