如何从postgres jsonb嵌套数组中获取字段

Question

我的表中有一个jsonb列。有一个数据

[
   {
      "name":"Гарнир",
      "num_max":1,
      "num_min":1,
      "modifications":[
         {
            "name":"Салат Вінегрет",
            "price":48,
            "brutto":1,
            "ingredient_id":33,
            "dish_modification_id":1
         },
         {
            "name":"Картошка фри",
            "price":24,
            "brutto":150,
            "ingredient_id":28,
            "dish_modification_id":2
         },
         {
            "name":"Говядина",
            "price":54,
            "brutto":150,
            "ingredient_id":19,
            "dish_modification_id":22
         },
         {
            "name":"Соус «Цезарь»",
            "price":27.6,
            "brutto":50,
            "ingredient_id":6,
            "dish_modification_id":23
         }
      ],
      "dish_modification_group_id":1
   },
   {
      "name":"Напитки",
      "num_max":1,
      "num_min":1,
      "modifications":[
         {
            "name":"Десерт Alphonso",
            "price":66,
            "brutto":1,
            "ingredient_id":11,
            "dish_modification_id":9
         }
      ],
      "dish_modification_group_id":4
   }
]

我需要通过product_id (整型表字段)和"dish_modification_id“来获取”价格“字段。该怎么做呢？

Answer 1

这是我的建议。当然，您可以添加where和其他子句。

with t(x) as 
(
 select j from jsonb_array_elements('... put your JSON data here ...'::jsonb) j
)
select (e ->> 'price')::numeric, (e ->> 'dish_modification_id')::numeric 
  from t 
 cross join lateral jsonb_array_elements(t.x -> 'modifications') e;

另一个(也许更干净的)版本。首先展平，然后选择。

with t(jsondata) as 
(
 select '... put your JSON data here ...'::jsonb
)
 select (e ->> 'price')::numeric, (e ->> 'dish_modification_id')::numeric 
   from t 
  cross join lateral jsonb_array_elements(jsondata) ext_array
  cross join lateral jsonb_array_elements(ext_array -> 'modifications') e;

运行示例here。