Page History

...

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

FROM folio_users.custom_fields AS cf

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 names named "values" (but you can name this object whatever you like)

...

LEFT JOIN folio_users.custom_fields__t AS cft  

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

         cft  ------ 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)

ON ORDER BY name, jsonb_extract_path_text (cfvalues.jsonb,'id')::uuid = cft.idORDER BY name, jsonb_extract_path_text (values.jsonb,'id'), value')

• Final note: 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 the "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:
values.ordinality AS custom_fields_ordinalityvalue')