Page History

...

CROSS JOIN LATERAL jsonb_array_elements (jsonb_extract_path (cf.jsonb, 'selectField', 'options', 'values')) AS values (jsonb)
----- "jsonb_array_elements" works on the 'values' array object at the end of the parentheses; we use this extract expression because "values" is an array
----- "jsonb_extract_path" works on the "selectField" and "options" objects – the objects listed must be in the same hierarchical order as they appear in the table
----- the entire cross join statement produces a jsonb object which I named "values" (note: what you name this object is totally arbitrary - you could call it "stuff" if you felt like it – "stuff (jsonb)")

LEFT JOIN folio_users.custom_fields__t AS cft  

...

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

• • Note: if
    • If you want to include "ordinality" as part of your results – this is the order in which the extracted values appear in the source record – you would insert "WITH ordinality" before "AS values (jsonb)":
             CROSS JOIN LATERAL jsonb_array_elements (jsonb_extract_path (cf.jsonb, 'selectField', 'options', 'values')) WITH ordinality AS values (jsonb)
    • Then you would add a line in the Select stanza to show the ordinality: 

...

• • • values.ordinality AScustom_fields_ordinality

RESULT:

Extracting Arrays 

...