gpt4 book ai didi

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

转载 作者:行者123 更新时间:2023-11-29 11:24:37 25 4
gpt4 key购买 nike

我在 PostgreSQL 中有一个 JSONB 对象:

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

我正在尝试在 SELECTWHERE 子句中使用其中“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

最佳答案

至少假设当前的 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"]'));

详细解释:

相关:

关于arrays - 在 WHERE IN 子句中使用来自 JSONB 数组的值,我们在Stack Overflow上找到一个类似的问题: https://stackoverflow.com/questions/38175349/

25 4 0
Copyright 2021 - 2024 cfsdn All Rights Reserved 蜀ICP备2022000587号
广告合作:1813099741@qq.com 6ren.com