如何过滤json列中嵌套值上的行？

Question

这是我的表（简体，仅重要的列）：

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"}}

我想选择键'Azamund'下任意位置的行name。像这样的东西：

# 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。

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

Answer 1

您的查询已关闭。
json_each()

是关键功能。还是jsonb_each()为了jsonb。进行了一些改进：

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

旧的sqlfiddle
db <>
fiddle在这里

• json_each()已经返回该值作为json数据类型。无需额外的演员表。

• 更好的是，在中使用LATERAL引用EXISTS。这比在SELECT列表中取消使用设置返回功能要干净得多。

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