postgresql - Postgres 在 jsonb 嵌套数组中查找

Question

我有一个案例，当我的数据在 jsonb 的嵌套数组中时，为了找到我必须执行多个 JSONB_ARRAY_ELEMENTS 的值，这很昂贵并且需要大量嵌套代码。

json 文件包含国家内部和城市内部的大陆。我需要访问城市值。

1. 有没有办法让这个查询更简单、更快？
2. 我试图使用 JSON_EXTRACT_PATH 解决它，但为了进入一个数组，但我需要索引。

WITH mydata AS (
    SELECT '
    {
   "continents":[
      {
         "name":"America",
         "area":43316000,
         "countries":[
            {
               "country_name":"Canada",
               "capital":"Toronto",
               "cities":[
                  {
                     "city_name":"Ontario",
                     "population":2393933
                  },
                  {
                     "city_name":"Quebec",
                     "population":12332
                  }
               ]
            },
            {
               "country_name":"Brazil",
               "capital":"Brasilia",
               "cities":[
                  {
                     "city_name":"Sao Paolo",
                     "population":34534534
                  },
                  {
                     "city_name":"Rio",
                     "population":445345
                  }
               ]
            }
         ]
      },
      {
         "name":"Europa",
         "area":10530751,
         "countries":[
            {
               "country_name":"Switzerland",
               "capital":"Zurich",
               "cities":[
                  {
                     "city_name":"Ginebra",
                     "population":4564565
                  },
                  {
                     "city_name":"Basilea",
                     "population":4564533
                  }
               ]
            },
            {
               "country_name":"Norway",
               "capital":"Oslo",
               "cities":[
                  {
                     "city_name":"Oslo",
                     "population":3243534
                  },
                  {
                     "city_name":"Steinkjer",
                     "population":4565465
                  }
               ]
            }
         ]
      }
   ]
}
'::JSONB AS data_column
)
SELECT cit.city->>'city_name' AS city,
       (cit.city->>'population')::INTEGER AS population
  FROM (SELECT JSONB_ARRAY_ELEMENTS(coun.country->'cities') AS city
          FROM (SELECT JSONB_ARRAY_ELEMENTS(cont.continent->'countries') AS country
                  FROM (SELECT JSONB_ARRAY_ELEMENTS(data_column->'continents') AS continent
                          FROM mydata
                       ) AS cont
                 WHERE cont.continent @> '{"name":"Europa"}'
               ) AS coun
         WHERE coun.country @> '{"country_name" : "Norway"}'
       ) AS cit
 WHERE cit.city @> '{"city_name": "Oslo"}'

看到我的嵌套查询了吗？看起来很难看，我可以使用以下方法得到答案:JSONB_EXTRACT_PATH( data_column->'continents', '1', 'countries', '1', 'cities', '0', 'population')但我必须对数组索引进行硬编码。

希望你能帮帮我。

谢谢。

Answer 1

你不需要任何嵌套，你可以做 lateral queries :

SELECT
  city->>'city_name' AS city,
  (city->>'population')::INTEGER AS population
FROM
  mydata,
  JSONB_ARRAY_ELEMENTS(data_column->'continents') AS continent,
  JSONB_ARRAY_ELEMENTS(continent->'countries') AS country,
  JSONB_ARRAY_ELEMENTS(country->'cities') AS city
WHERE continent ->> 'name' = 'Europa'
  AND country ->> 'country_name' = 'Norway'
  AND city ->> 'city_name' = 'Oslo';

_{( online demo )}

但是，由于您提到了路径并且必须在其中指定索引，这实际上是 Postgres 12 的完美用例 JSON paths :

SELECT jsonb_path_query(data_column, '$.continents[*]?(@.name == "Europa").countries[*]?(@.country_name=="Norway").cities[*]?(@.city_name=="Oslo")') FROM mydata

_{( online demo )}