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

概述我在PostgreSQL中有一个 JSONB对象： '{"cars": ["bmw", "mercedes", "pinto"], "user_name": "ed"}' 我试图在SELECT的WHERE子句中使用其中“cars”数组的值： SELECT car_id FROM cars WHERE car_type IN ('bmw', 'mercedes', 'pinto'); 这将正确返回值 我在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","user_name": "ed"}'::Json#>>'{cars}';            -- this returns 'bmw','pinto'        SELECT car_ID FROM cars WHERE car_type IN ( v_car_results );    END

但是,SELECT语句不返回任何行.我知道它正在阅读这3种车型作为单一类型. (如果我只在“cars”元素中包含一个car_type,则查询工作正常.)

我如何将这些值视为WHERE子句中的数组？

我尝试过其他一些东西：

> ANY条款.
>各种铸造尝试.
>这些查询：

SELECT car_ID FROM carsWHERE car_type IN (Json_array_elements_text('["bmw","pinto"]'));...WHERE car_type IN ('{"cars": ["bmw","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 carsWHERE car_type IN ('bmw','pinto'); -- Returns Values : 1,2,3

解决方法 假设当前的Postgres版本为9.5,因为它没有指定.

使用set-returns函数jsonb_array_elements_text()(作为表函数！)并加入到结果中：

SELECT c.car_IDFROM   Jsonb_array_elements_text('{"cars": ["bmw","user_name": "ed"}'::Jsonb->'cars') t(car_type)JOIN   cars c USING (car_type);

使用Jsonb->’cars’从对象中提取JsON数组,并将生成的JsON数组(仍为数据类型Jsonb)传递给该函数. (运算符#>也可以完成这项工作.)

旁边::: Json#>>不仅仅是一个 *** 作符.它是对Json(:: Json)的强制转换,后跟运算符#>>.你也不需要.

生成的类型文本可以方便地匹配列类型varchar(255),因此我们不需要类型转换.并指定列名car_type以允许在连接条件中使用USING的语法简写.

这种形式更短,更优雅,通常比使用IN()或= ANY()的替代品快一点 – 这也可以.您的尝试非常接近,但您需要带有子查询的变体.这可行：

SELECT car_ID FROM carsWHERE  car_type IN (SELECT Json_array_elements_text('["bmw","pinto"]'));

或者,清洁：

SELECT car_ID FROM carsWHERE  car_type IN (SELECT * FROM Json_array_elements_text('["bmw","pinto"]'));

详细说明：

> How to use ANY instead of IN in a WHERE clause with Rails?

有关：

> How to turn json array into postgres array?

总结

以上是内存溢出为你收集整理的数组 – 在WHERE IN子句中使用JSONB数组中的值全部内容，希望文章能够帮你解决数组 – 在WHERE IN子句中使用JSONB数组中的值所遇到的程序开发问题。

如果觉得内存溢出网站内容还不错，欢迎将内存溢出网站推荐给程序员好友。