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

Question

我在 PostgreSQL 中有一个 JSONB 对象:

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

我正在尝试在 SELECT 的 WHERE 子句中使用其中“cars”数组中的 values:

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 子句中的数组？

我已经尝试了一些其他的东西:

1. ANY 子句。

2. 各种施放的尝试。

3. 这些查询:

   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

Answer 1

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

使用set-returning函数jsonb_array_elements_text() (作为表函数!)并加入到结果:

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);

使用 jsonb->'cars' 从对象中提取 JSON 数组，并将生成的 JSON 数组(仍然是数据类型 jsonb)传递给函数。 (运算符 #> 也可以完成这项工作。)

旁白:::json#>> 不仅仅是一个运算符。它是对 json (::json) 的转换，后跟 the operator #>> .你也不需要。

生成的类型 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"]'));

详细解释:

• How to use ANY instead of IN in a WHERE clause?

相关:

• How to turn JSON array into Postgres array?