Page History

...

In the previous examples, the elements being extracted were "objects" within the JSON array, denoted by curly brackets. In this next example, we need to extract multiple values from an "array" element (denoted by square brackets) that is embedded in a hierarchy of objects object elements. This calls for nested json extract statements, a strucure very similar to what we used in Access with nested "GetField" and "GetXXXBLOB" functions.

• Nested extract statements are applied from the outside in. So the left-most json statement applies to the right-most element in the parentheses
• The nested extract statement is cast as a "jsonb" object
• That jsonb object is then used in the select stanza to get the needed fields (by using another json extract statment statement on it)

The example below uses the folio_users.custom_fields table, which has embedded array elements ("id","value" and "default") nested within a hierarchy of objects: 

...

The code to do that is:

SELECT

   cft.id as AS custom_fields_id,

   cft.ref_id,

...

   jsonb_extract_path_text (values.jsonb,'value') as value_name
           ------ because "values" is a jsonb object (produced by the cross join below), I we need to use json extract statements in "Select" to get the value_id and the value_name from "values"

...

CROSS JOIN LATERAL jsonb_array_elements (jsonb_extract_path (cf.jsonb, 'selectField', 'options', 'values')) AS values (jsonb)
----- "jsonb_array_elements" works on the 'values' element in parentheses; it specifies that "values" is an array
----- "jsonb_extract_path" works on the "selectField" and "options" objects – the order of objects listed must be the same hierarchical order as they appear in the table
----- the entire cross join statement produces a jsonb object named "values" (but what you can name this object whatever you likeis totally arbitrary)

LEFT JOIN folio_users.custom_fields__t AS cft  

ON jsonb_extract_path_text (cf.jsonb,'id')::uuid UUID = cft.id

         ------ I'm joining to the custom_fields__t table to show the custom fields id, the ref_id and the custom_fields name (this is not necessary, it's just a convenience)

...

ORDER BY name, jsonb_extract_path_text (values.jsonb,'id')::UUID, jsonb_extract_path_text (values.jsonb,'value')

...

Then you would add a line in the Select stanza:
values.ordinality AS custom_fields_ordinality

RESULT:

Image Added