Search and extract element located in various path of json structure

Question

I have a json in a PostgreSQL database and I need to extract an array not always located in same place.

Problem

• Need to extract array choicies of a particular element name
• Element name is known, but not where he's sitting in structure

Rules

• All elements name are unique
• choicies attribute could not be present

JSON structure

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 : [...]
      }
    ]
  }         
]

My try to extract elements by flatten the structure

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

Alas column choicies is always null in my results. And that will not work when element is located somewhere else, like

• page.elements.templateElements
• page.templateElements
• ... and so on

I don't know if there is a way to search for a key (name) wherever it's sitting in json structure and extract an other key (choicies).

I wish to call a select with element name in parameter return choicies of this element.

By instance, if I call select with element name (element1 or element4 or element8), choicies array (as rows or json or text, no preference here) of this element should be return.

Answer 1

Wow! Solution founded goes beyond expectation! JSONPath was the way to go

Amazing what we can do with this.

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

Explanation of jsonpath in SQL

jsonb_path_query(jsonb_data, '$.** ? (@.name == "element_name_to_look_at")')->'choices'

• jsonb_path_query : posgresql jsonpath function
• jsonb_data : database column with jsonb data or jsonb expression
• $.** : search everywhere from root element
• ? : where clause / filter
• @ : object return by search
• @.name == "element_name_to_look_at" : every object name equals element_name_to_look_at
• ->'choices' : for each object returned by jsonpath, get choices attribute

Final version

After get choices jsonb array, we return a dataset with every choice.

choices arrays look like this :

[{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);