Использовать значения из массива JSONB внутри предложения WHERE IN

Question

У меня есть JSONB Объект в PostgreSQL:

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

Я пытаюсь использовать значения из массива "автомобили" внутри него в WHERE пункт о SELECT:

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 типа автомобилей как один тип. (Если я включу только один car_type в элементе "cars" запрос работает нормально.)

Как бы я относился к этим значениям как массив внутри 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, так как она не была указана.}

Используйте функцию возврата набора 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);

Извлеките массив 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"]'));

Детальное объяснение:

• Как использовать ЛЮБОЙ вместо IN в предложении WHERE с Rails?

Связанные с:

• Как превратить массив JSON в массив Postgres?