I have a JSONB
object in PostgreSQL:
我在PostgreSQL中有一个JSONB对象:
'{"cars": ["bmw", "mercedes", "pinto"], "user_name": "ed"}'
I am trying to use values from the "cars" array inside it in the WHERE
clause of a SELECT
:
我试图在SELECT的WHERE子句中使用其中“cars”数组的值:
SELECT car_id FROM cars WHERE car_type IN ('bmw', 'mercedes', 'pinto');
This will correctly return the values 1, 2, and 3 - see table setup at the bottom of this post.
这将正确返回值1,2和3 - 请参阅本文底部的表格设置。
Currently, in my function I do this:
目前,在我的功能中我这样做:
(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
However, the SELECT
statement is not returning any rows. I know it's reading those 3 car types as a single type. (If I only include one car_type
in the "cars" element, the query works fine.)
但是,SELECT语句不返回任何行。我知道它正在阅读这3种车型作为单一类型。 (如果我只在“cars”元素中包含一个car_type,则查询工作正常。)
How would I treat these values as an array inside the WHERE
clause?
我如何将这些值视为WHERE子句中的数组?
I've tried a few other things:
我尝试过其他一些事情:
-
The
ANY
clause.ANY条款。
-
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');
这些查询:SELECT car_id FROM cars WHERE car_type IN(json_array_elements_text('[“bmw”,“mercedes”,“pinto”]')); ... 在哪里car_type IN('{“cars”:[“bmw”,“mercedes”,“pinto”],“user_name”:“ed”}':json - >>'cars');
It feels like it's something simple I'm missing. But I've fallen down the rabbit hole on this one. (Maybe I shouldn't even be using the ::json#>>
operator?)
这感觉就像我想念的那样简单。但是我在这个兔子洞里掉了下来。 (也许我甚至不应该使用:: json#>>运算符?)
TABLE SETUP
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
1 个解决方案
#1
5
Assuming the current Postgres version 9.5 since it wasn't specified.
假设当前的Postgres版本为9.5,因为它没有指定。
Use the set-returning function jsonb_array_elements_text()
(as table function!) and join to the result:
使用set-returns函数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);
Extract the JSON array from the object with jsonb->'cars'
and pass the resulting JSON array (still data type jsonb
) to the function. (The operator #>
would do the job as well.)
使用jsonb - >'cars'从对象中提取JSON数组,并将生成的JSON数组(仍为数据类型jsonb)传递给该函数。 (操作员#>也可以完成这项工作。)
Aside: ::json#>>
isn't just an operator. It's a cast to json (::json
), followed by the operator #>>
. You don't need either.
旁白::: json#>>不仅仅是一个运营商。它是对json(:: json)的强制转换,后跟运算符#>>。你也不需要。
The resulting type text
conveniently matches your column type varchar(255)
, so we don't need type-casting. And assign the column name car_type
to allow for the syntax shorthand with USING
in the join condition.
生成的类型文本可以方便地匹配列类型varchar(255),因此我们不需要类型转换。并指定列名car_type以允许在连接条件中使用USING的语法简写。
This form is shorter, more elegant and typically a bit faster than alternatives with IN ()
or = ANY()
- which would work too. Your attempts were pretty close, but you need the variants with a subquery. This would work:
这种形式更短,更优雅,通常比使用IN()或= ANY()的替代品快一点 - 这也可以。您的尝试非常接近,但您需要具有子查询的变体。这可行:
SELECT car_id FROM cars
WHERE car_type IN (SELECT json_array_elements_text('["bmw", "mercedes", "pinto"]'));
Or, cleaner:
SELECT car_id FROM cars
WHERE car_type IN (SELECT * FROM json_array_elements_text('["bmw", "mercedes", "pinto"]'));
Detailed explanation:
- How to use ANY instead of IN in a WHERE clause with Rails?
如何在带有Rails的WHERE子句中使用ANY而不是IN?
Related:
- How to turn json array into postgres array?
如何将json数组转换为postgres数组?
#1
5
Assuming the current Postgres version 9.5 since it wasn't specified.
假设当前的Postgres版本为9.5,因为它没有指定。
Use the set-returning function jsonb_array_elements_text()
(as table function!) and join to the result:
使用set-returns函数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);
Extract the JSON array from the object with jsonb->'cars'
and pass the resulting JSON array (still data type jsonb
) to the function. (The operator #>
would do the job as well.)
使用jsonb - >'cars'从对象中提取JSON数组,并将生成的JSON数组(仍为数据类型jsonb)传递给该函数。 (操作员#>也可以完成这项工作。)
Aside: ::json#>>
isn't just an operator. It's a cast to json (::json
), followed by the operator #>>
. You don't need either.
旁白::: json#>>不仅仅是一个运营商。它是对json(:: json)的强制转换,后跟运算符#>>。你也不需要。
The resulting type text
conveniently matches your column type varchar(255)
, so we don't need type-casting. And assign the column name car_type
to allow for the syntax shorthand with USING
in the join condition.
生成的类型文本可以方便地匹配列类型varchar(255),因此我们不需要类型转换。并指定列名car_type以允许在连接条件中使用USING的语法简写。
This form is shorter, more elegant and typically a bit faster than alternatives with IN ()
or = ANY()
- which would work too. Your attempts were pretty close, but you need the variants with a subquery. This would work:
这种形式更短,更优雅,通常比使用IN()或= ANY()的替代品快一点 - 这也可以。您的尝试非常接近,但您需要具有子查询的变体。这可行:
SELECT car_id FROM cars
WHERE car_type IN (SELECT json_array_elements_text('["bmw", "mercedes", "pinto"]'));
Or, cleaner:
SELECT car_id FROM cars
WHERE car_type IN (SELECT * FROM json_array_elements_text('["bmw", "mercedes", "pinto"]'));
Detailed explanation:
- How to use ANY instead of IN in a WHERE clause with Rails?
如何在带有Rails的WHERE子句中使用ANY而不是IN?
Related:
- How to turn json array into postgres array?
如何将json数组转换为postgres数组?