假设我们必须使用PostgreSQL 9.5+数据库上的metadata
JSONB列查询user
表。
1.通过一级属性的值选择项(第一种方式)
您可以使用@>
运算符查询metadata
。 该运算符可以将部分JSON字符串与JSONB列进行比较。 它是遏制运营商。 对于这种情况,您可能需要在metadata
列上添加GIN索引。
SELECT * FROM users WHERE metadata @> '{"country": "Peru"}' ;
2.通过一级属性的值选择项(第2种方式)
->>
运算符获取一个JSON对象字段作为文本。 如果要查询JSONB列中的简单字段,请使用它。 您可以在metadata->>'country'
上添加B树索引。
SELECT * FROM users WHERE metadata->> 'country' = 'Peru' ;
3.选择项目属性值
再次, ->>
运算符获取一个JSON对象字段作为文本。 只需在SELECT
直接使用它即可。
SELECT metadata->> 'country' FROM users ;
4.仅选择存在特定属性的项目
您可以将->>
运算符与用于文本的经典运算符结合使用: =
, <>
, IS NULL
等。不要忘记使用B树索引为metadata->>'country'
进行索引。
SELECT * FROM users WHERE metadata->> 'country' IS NOT NULL ;
5.通过嵌套属性的值选择项目
您可以同时使用@>
或->>
,就像第一级属性一样。 根据您的选择添加索引。
SELECT *
FROM users
WHERE metadata-> 'company' ->> 'name' = "Mozilla" ;
SELECT *
FROM users
WHERE metadata @> '{"company":{"name": "Mozilla"}}' ;
6.通过数组中的属性值选择项目
记住@>
运算符检查JSONB列中的包含,您可以通过传递{"x":["a"]}
来查询类似{"x": ["a", "b", "c"]"}
的数组{"x":["a"]}
到WHERE
子句:
SELECT *
FROM users
WHERE metadata @> '{"companies": ["Mozilla"]}' ;
7. IN运算符的属性
有时,我们可能需要选择JSONB列中的属性与一堆可能的值匹配的项目。
SELECT *
FROM users
WHERE metadata->> 'countries' IN ( 'Chad' , 'Japan' );
8.插入整个对象
照常使用UPDATE ... SET
,并将整个对象作为JSON传递。
UPDATE users SET metadata = '{"country": "India"}' ;
9.更新或插入属性
使用||
运算符,将实际数据与新数据连接在一起。 它将更新或插入值。
UPDATE users
SET metadata = metadata || '{"country": "Egypt"}' ;
10.删除属性
操作员-从对象中删除键。
UPDATE users SET metadata = metadata - 'country' ;
最后说明
JSONB对象的查询几乎与经典SQL查询一样简单。 我仅在此处发布了一些示例,说明了对我来说最常见的用例。 这是对我的说明,我希望它也可以对其他人有所帮助。
您可以在PostgreSQL文档中进行挖掘,其中包含更多示例和更精确的解释:
PostgreSQL:文档:9.6:JSON函数和运算符
注意:json和jsonb类型的这些运算符都有并行的变体。 字段/元素/路径… www.postgresql.org PostgreSQL:
文档:9.6:JSON类型
PostgreSQL每个数据库只允许一种字符集编码。 因此,JSON类型不可能... www.postgresql.org
随时提出建议,反馈和批评意见。 我会很高兴学习更多。
感谢Emilien Schneider (再次一次)的审阅。
From: https://hackernoon.com/how-to-query-jsonb-beginner-sheet-cheat-4da3aa5082a3