时间戳

首页 > 解决方案 > 搜索和提取位于 json 结构的各种路径中的元素

jsonb - 搜索和提取位于 json 结构的各种路径中的元素

问题描述

我在 PostgreSQL 数据库中有一个 json，我需要提取一个并不总是位于同一个地方的数组。

问题

规则

JSON结构

pages : [
  {
    name : 'page1',
    elements : [
      { name : 'element1', choicies : [...]},
      { name : 'element2', choicies : [...]}
    ]
  }, {
    name : 'page2',
    elements : [
      {
        name : 'element3',
        templateElements : [
          {
            name : 'element4'
            choicies : [...]
          }, {                  
            name : 'element5'
            choicies : [...]
          }
       ]
      }, {
        name : 'element6'
        choicies : [...]
      }
    ]
  },{
    name : 'element7',
    templateElements : [
      {
        name : 'element8'
        choicies : [...]
      }
    ]
  }         
]

我尝试通过展平结构来提取元素

SELECT  pages::jsonb->>'name',
        pageElements::jsonb ->> 'name',
        pageElements::jsonb -> 'choicies',
        pages.*
FROM    myTable as myt,
        jsonb_array_elements(myt.json -> 'pages') as pages,
        jsonb_array_elements(pages -> 'elements') as pageElements

唉choicies，我的结果中的列始终为空。当元素位于其他地方时，这将不起作用，例如

我不知道是否有一种方法可以搜索位于 json 结构中的键（名称）并提取其他键（选项）。

我希望在该元素的参数返回选择中调用带有元素名称的选择。

例如，如果我使用元素名称（element1 或 element4 或 element8）调用 select，则应返回此元素的 choicies 数组（如行或 json 或文本，此处没有偏好）。

标签： jsonbpostgresql-12

解决方案

哇！解决方案创立去属于期望！JSONPath是要走的路

令人惊讶的是，我们可以用它做些什么。

SQL

-- Use jsonpath to search, filter and return what's needed
SELECT  jsonb_path_query(
            myt.jsonb,
            '$.** ? (@.name == "element_name_to_look_at")'
        )->'choices' as jsonbChoices
FROM    myTable as myt

SQL中jsonpath的解释

jsonb_path_query(jsonb_data, '$.** ? (@.name == "element_name_to_look_at")')->'choices'
  • jsonb_path_query: posgresql jsonpath 函数
  • jsonb_data: 带有 jsonb 数据或 jsonb 表达式的数据库列
  • $.**: 从根元素处搜索
  • ?: where 子句/过滤器
  • @: 通过搜索返回对象
  • @.name == "element_name_to_look_at": 每个对象都name等于element_name_to_look_at
  • ->'choices': 对于 jsonpath 返回的每个对象，获取choices属性

最终版本

得到choicesjsonb 数组后，我们返回一个包含所有选择的数据集。

choices数组看起来像这样：

[{value:'code1',text:'Code Label 1'}, {value:'code2',text:'Code Label 2'},...]

SELECT  choices.*
FROM    (
        -- Use jsonpath to search, filter and return what's needed
        SELECT  jsonb_path_query(myt.jsonb, '$.** ? (@.name == "element_name_to_look_at")')->'choices' as jsonbChoices
        FROM    myTable as myt
) choice,
-- Explode json return array into columns
jsonb_to_recordset(choice.jsonbChoices) as choices(value text, text text);

推荐阅读