在 WHERE IN 子句中使用来自 JSONB 数组的值

Question

在 WHERE IN 子句中使用来自 JSONB 数组的值

arrayspostgresqlwhere-clausejsonbset-returning-functions

10

我在PostgreSQL中有一个JSONB对象：

'{"cars": ["bmw", "mercedes", "pinto"], "user_name": "ed"}'

我正在尝试在SELECT语句的WHERE子句中使用 "cars" 数组中的值：

SELECT car_id FROM cars WHERE car_type IN ('bmw', 'mercedes', 'pinto');

这将正确返回值1、2和3——请参见本帖底部的表格设置。

目前，在我的函数中，我这样做：

(1) Extract the "cars" array into a variable `v_car_results`.
(2) Use that variable in the `WHERE` clause.

Pseudo code:

    DECLARE v_car_results TEXT
    BEGIN
        v_car_results = '{"cars": ["bmw", "mercedes", "pinto"], "user_name": "ed"}'::json#>>'{cars}';
            -- this returns 'bmw', 'mercedes', 'pinto'
        SELECT car_id FROM cars WHERE car_type IN ( v_car_results );
    END

然而，SELECT语句没有返回任何行。我知道它将这3种汽车类型看作是一个单个的类型。（如果我只在“cars”元素中包含一种car_type，查询就可以正常工作。）

我该如何在WHERE子句中将这些值视为一个数组呢？

我尝试了其他一些方法：

The ANY clause.
Various attempts at casting.

These queries:

SELECT car_id FROM cars
WHERE car_type IN (json_array_elements_text('["bmw", "mercedes", "pinto"]'));

...
WHERE car_type IN ('{"cars": ["bmw", "mercedes", "pinto"], "user_name": "ed"}':json->>'cars');

感觉我错过了某些简单的东西。但在这个问题上，我已经陷入了困境。（也许我甚至不应该使用::json#>>运算符？）

表格设置

CREATE TABLE cars (
   car_id SMALLINT
 , car_type VARCHAR(255)
);

INSERT INTO cars (car_id, car_type)
VALUES
  (1, 'bmw')
, (2, 'mercedes')
, (3, 'pinto')
, (4, 'corolla');

SELECT car_id FROM cars
WHERE car_type IN ('bmw', 'mercedes', 'pinto'); -- Returns Values : 1, 2, 3

- bjones1831

1个回答

网页内容由stack overflow 提供, 点击上面的

可以查看英文原文，
原文链接

- Erwin Brandstetter · Accepted Answer

^{假设至少使用当前的Postgres 9.5。}

使用返回集函数jsonb_array_elements_text()（作为表函数！）并将其与join结果连接：

SELECT c.car_id
FROM   jsonb_array_elements_text('{"cars": ["bmw", "mercedes", "pinto"]
                                 , "user_name": "ed"}'::jsonb->'cars') t(car_type)
JOIN   cars c USING (car_type);

从对象中提取JSON数组，使用jsonb->'cars'并将结果JSON数组（仍然是jsonb数据类型）传递给函数。（操作符#>也可以完成任务。）

另外：::json#>>不仅仅是一个操作符。它是一个转换为json的强制类型转换（::json），后面跟着操作符#>>。你不需要这两个。

生成的类型text与您的列类型varchar(255)方便匹配，因此我们不需要类型转换。并且将列名称car_type分配给允许在连接条件中使用USING的语法缩写。

这种形式比使用IN ()或= ANY()的替代方法更短、更优雅，通常也稍微快一些。您的尝试非常接近，但需要使用带有子查询的变体。这将起作用：

SELECT car_id FROM cars
WHERE  car_type IN (SELECT json_array_elements_text('["bmw", "mercedes", "pinto"]'));

或者，更简洁一点：

SELECT car_id FROM cars
WHERE  car_type IN (SELECT * FROM json_array_elements_text('["bmw", "mercedes", "pinto"]'));

详细说明：

如何在WHERE子句中使用ANY代替IN？