如何从postgres json字段中选择包含某些值的所有记录containsig一个数组 [英] how to select all records containing certain values from a postgres json field containig an array

问题描述

我有 tableA ，其中包含一个json字段 data 。字段 data 的抽象示例：

I have tableA containing a json field data. abstracted examples for field data:

{"sequence": [123,456,789]}
{"sequence": [456,789]}
{"sequence": [789, 12]}

更新：：使用一些示例数据添加了sqlfiddle-> http://sqlfiddle.com/#!17/62475/24

update: added sqlfiddle with some sample data -> http://sqlfiddle.com/#!17/62475/24

with

select * from tableA where (data->'sequence') @> '[456]';

我能够选择所有包含 456 ：

{"sequence": [123,456,789]}
{"sequence": [456,789]}

我正在努力选择所有包含 123 的记录或 456 。

I am struggling to select all records containing 123 or 456. is this possible?

添加 123 或 456也是很有用的作为子查询，例如：

it would also be useful to include 123 or 456 as a subquery like:

select * from tableA where (data->'sequence') in (select data_id from tableB);

推荐答案

使用 ANY 以测试jsonb数组是否包含正确值的任何，可以是数组或子查询，请使用您的sqlfiddle示例

Use ANY to to test if the jsonb array contains any of the right values, which can be an array or a subquery, using your sqlfiddle example

SELECT *
FROM tableA
WHERE (data->'sequence') @> ANY(SELECT (data_id::TEXT)::JSONB FROM tableB)

您还可以传递数组文字，在这种情况下，它需要一个JSONB值数组，即 @> 的右侧可以用文字 ANY（' {123,456}':: JSONB []）

You can also pass an array literal, in this case it would require an array of JSONB values, i.e. the right side of @> could be replaced with the literal ANY('{123,456}'::JSONB[])

或者，使用& 测试数组重叠。首先必须将JSON / JSONB数组转换为本地数组

Alternatively, Use the && to test for array overlap. It is first necessary to convert the JSON/JSONB array to a native array

SELECT tableA.*
FROM tableA 
JOIN LATERAL (
  SELECT ARRAY_AGG(v::INT) y 
  FROM JSONB_ARRAY_ELEMENTS_TEXT(data->'sequence') v
) x ON TRUE
WHERE x.y && '{123, 456}'

您也可以替换数组文字' {123，456}'和一个返回整数数组的子查询，例如（SELECT ARAY_AGG（data_id）FROM tableB）

You can also replace the array literal '{123, 456}' with a subquery that returns an array of integers, such as (SELECT ARRAY_AGG(data_id) FROM tableB)

另一种选择是使用或在where子句中

Another option would be to use or in your where clause

select *
from tableA 
where (data->'sequence') @> '[456]'
   or (data->'sequence') @> '[123]'

这篇关于如何从postgres json字段中选择包含某些值的所有记录containsig一个数组的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！