Page History

...

This method captures the array elements (and other json elements) by using embedded extract statements in the Select stanza. In this example, we're using elements from the folio_inventory.instance table:

-- This query gets Get the json extracts in the first query, then aggregates aggregate them in the second query. We cannot use cross joins, because records without all the extracted seleted array elements will drop out. 

...

ii.jsonb #>> '{hrid}' AS instance_hrid, ---- first level text object extract

ii.jsonb #>> '{title}' AS title, ---- first level text object extract

jsonb_extract_path_text (jsonb_array_elements (jsonb_extract_path (ii.jsonb, 'contributors')),'name') AS contributors, -- contributors array extract (second-level arrayextract)

(jsonb_array_elements (jsonb_extract_path (ii.jsonb,'languages'))) #>>'{}' as languages, ----- languages array extract (top-level array) – note empty curly brackets

jsonb_extract_path_text (jsonb_array_elements (jsonb_extract_path (ii.jsonb, 'subjects')),'value') as subjects ----- subject array extract (second-level arrayextract)

FROM folio_inventory.instance AS ii

...