*** This version of Confluence is for testing only and contains a copy of content from June 29th 2026. No changes will be preserved. ***
...
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 finding elements from the the folio_inventory.instance table (contributors, languages, and subjects):
-- Get the json extracts in the first query, then aggregate them in the second query. We cannot use cross joins, because records without all ALL the seleted array elements will drop out.
...
(jsonb_array_elements (jsonb_extract_path (ii.jsonb,'languages'))) #>>'{}' as languages, ----- languages array extract (top-level arrayextract) – note empty curly brackets
...