用于过滤嵌套对象或数组中的JSON数据的WHERE条件

beq87vna  于 12个月前  发布在  其他
关注(0)|答案(1)|浏览(165)

我在PostgreSQL数据库中有一个表

CREATE TABLE js.orders (
    id serial NOT NULL PRIMARY KEY,
    info json NOT NULL
)

字符串
包含6行JSON数据:

select * from js.orders;


返回

id |                                                info                                                
----+----------------------------------------------------------------------------------------------------
  1 | { "customer": "Kapil", "items": {"product": "Heineken","qty": 6}}
  2 | { "customer": "Satyen", "items": {"product": "Heineken","qty": 18}}
  3 | { "customer": "Rekha", "items": {"product": "Carlsberg","qty": 24}}
  4 | { "customer": "Madhuri", "items": {"product": "Kalyani","qty": 12}}
  5 | { "customer": "Srinivas", "items": {"product": "Kingfisher Strong","qty": 12}}
  6 | { "customer": "Saina", "items": [{"product": "Bira91","qty": 6},{"product": "Kalyani","qty": 6} ]}
(6 rows)


下面是带有“where”条件的“select”查询:

SELECT info ->> 'customer' AS customer FROM js.orders WHERE info -> 'items' ->> 'product' = 'Heineken';


返回正确的两行:

customer 
----------
 Kapil
 Satyen
(2 rows)


但是,以下查询应返回两行:

SELECT info ->> 'customer' AS customer FROM js.orders WHERE info -> 'items' ->> 'product' = 'Kalyani';


但只返回一行:

customer     
-----------------
 Madhuri
(1 row)


很明显,“customer”:“Sania”的行有一个“items”的LIST而不是一个“items”,这是导致这个问题的原因。我应该如何修改我的查询,以返回正确的行数。或者是数据必须重新格式化。
平台

'PostgreSQL 14.10 (Ubuntu 14.10-0ubuntu0.22.04.1) on x86_64-pc-linux-gnu, compiled by gcc (Ubuntu 11.4.0-1ubuntu1~22.04) 11.4.0, 64-bit'

vsdwdz23

vsdwdz231#

你的第一个问题是,你将结构化数据存储为JSON文档,而不是关系设计中的普通列,这对于存储和查询来说会更有效。
你的第二个问题是你不需要改变JSON文档的结构。有些有一个 object 和一个 array 作为关键字“items”的值。(没有一个有一个“list”,这在JSON terminology中不存在。)使查询更加复杂。
虽然坚持了你不幸的设计,但这个带有jsonpath运算符**@?**的查询是有效的,因为它在默认的“lax”模式下处理给定路径上的对象 * 和 * 数组:

SELECT info ->> 'customer' AS customer
FROM   orders
WHERE  info -> 'items' @? '$.product ? (@ == "Kalyani")';

字符串
只查看像原始查询一样的 * 对象 *:

SELECT id, info ->> 'customer' AS customer
FROM   orders
WHERE  info -> 'items' @? 'strict $.product ? (@ == "Kalyani")';


fiddle
需要Postgres 12或更高版本,其中添加了SQL/JSON路径语言。并假设jsonb作为数据类型,而不是json。无论如何,前者通常是首选。
您可能需要索引支持。请参阅:

  • Postgres jsonb查询动态值

相关问题