sql - 在嵌套的 jsonb 中选择 - postgresql

Question

我很难在基础上找到值，因为所有示例都引用了在简单 json 中查找信息的相同方式。

但是一个工作的朋友给了我一个解决方案，我来分享一下。

最初的问题是：如何在嵌套的 json 中进行选择？？？

像这样的json：

{
   "vehicle":[
      {
         "vehicle_type":"Truck",
         "car_make":"Lotus",
         "car_model":"Esprit",
         "quantity":7,
         "seats":7,
         "price_hour":16,
         "price_day":147,
         "color":[
            "Purple",
            "Pink",
            "Blue",
            "White"
         ]
      }
   ]
}

要查看结构，您可以使用https://jsoneditoronline.org/

标签： sqljsonpostgresqljsonbpostgresql-9.6

Answer 1

为了确定车辆颜色为 blue，车辆类型为SUV，jsonb_array_elements()可以使用函数取消嵌套主数组，然后放入(j.elm->>'vehicle_type') = 'SUV'子句WHERE中就足够了，而(j.elm->>'color')::jsonb ? 'Blue'应该使用包含jsonb转换的? 第一个运算符，因为(j.elm->>'color')提取数组，而(j.elm->>'vehicle_type')执行简单的字符串片段。

因此不需要正则表达式，例如以下查询：

SELECT e.*
  FROM example e
 CROSS JOIN jsonb_array_elements(jsonTest->'vehicle') j(elm)
 WHERE (j.elm->>'color')::jsonb ? 'Blue'

和

SELECT e.*
  FROM example e
 CROSS JOIN jsonb_array_elements(jsonTest->'vehicle') j(elm)
 WHERE (j.elm->>'vehicle_type') = 'SUV'

Demo