这是我的表(简体,仅重要的列):
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。像这样的东西:
'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?
您的查询已关闭。json_each()*是关键功能。还是jsonb_each()为了jsonb。进行了一些改进:
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
在Postgres 12中使用SQL / JSON可以变得更简单/更高效…