我正试图从Postgres JSON列中搜索一条记录。存储的数据有一个类似的结构
{
"contract_shipment_date": "2015-06-25T19:00:00.000Z",
"contract_product_grid": [
{
"product_name": "Axele",
"quantity": 22.58
},
{
"product_name": "Bell",
"quantity": 52.58
}
],
"lc_status": "Awaited"
}
我的表名是Heap，列名是contract_product_grid。此外，contract_product_grid列可以包含多个产品记录。我找到了这个文档，但无法获得所需的输出。
必要的情况是，我有一个过滤器，用户可以在其中选择product_name，并且根据使用matching子句输入的名称，记录将被提取并返回给用户。
假设您输入Axele作为product_name输入，并希望返回quantity键的匹配值。
然后使用：
SELECT js2->'quantity' AS quantity
FROM
(
SELECT JSON_ARRAY_ELEMENTS(value::json) AS js2
FROM heap,
JSON_EACH_TEXT(contract_product_grid) AS js
WHERE key = 'contract_product_grid'
) q
WHERE js2->> 'product_name' = 'Axele'
其中将最外层的JSON扩展为key&value对至JSON_EACH_TEXT(json)，并通过JSON_ARRAY_ELEMENTS(value::json)函数将具有新形成的阵列的所有元素分割。
然后，通过主查询中指定的product_name进行过滤。
演示
p.S.不要忘记用大括号包装JSON列的值
SELECT *
FROM
(
SELECT JSON_ARRAY_ELEMENTS(contract_product_grid::json) AS js2
FROM heaps
WHERE 'contract_product_grid' = 'contract_product_grid'
) q
WHERE js2->> 'product_name' IN ('Axele', 'Bell')
正如我在问题中提到的那样，我的列名是"contract_product_grid"，我只需要从中搜索。使用这个查询，我可以使用输入产品名称的IN子句获得contract_product_grid信息。
您需要运行数组，以便能够对每个值使用LIKE条件：
select h.*
from heap h
where exists (select *
from jsonb_array_elements(h.contract_product_grid -> 'contract_product_grid') as p(prod)
where p.prod ->> 'product_name' like 'Axe%')
如果你真的不需要通配符搜索(所以=而不是LIKE(，你可以使用更高效的包含运算符@>：
select h.*
from heap h
where h.contract_product_grid -> 'contract_product_grid' @> '[{"product_name": "Axele"}]';
这也可以用于搜索多种产品：
select h.*
from heap h
where h.contract_product_grid -> 'contract_product_grid' @> '[{"product_name": "Axele"}, {"product_name": "Bell"}]';
如果您使用Postgres12，您可以使用JSON路径表达式简化它：
select *
from heap
where jsonb_path_exists(contract_product_grid, '$.contract_product_grid[*].product_name ? (@ starts with "Axe")')
或者使用正则表达式：
select *
from heap
where jsonb_path_exists(contract_product_grid, '$.contract_product_grid[*].product_name ? (@ like_regex "axe.*" flag "i")')