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

beq87vna 于 12个月前发布在其他

关注(0)|答案(1)|浏览(164)

我在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'

型

JSON

来源：https://stackoverflow.com/questions/77772487/where-condition-to-filter-json-data-in-nested-object-or-array

1条答案

按热度按时间

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查询动态值

赞(0）回复(0）举报 12个月前

我来回答

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

1条答案

相关问题

热门标签

最新问答