*** This version of Confluence is for testing only and contains a copy of content from June 29th 2026. No changes will be preserved. ***
...
WITH recs AS ----- Get the array elements for contributors, languages and subject subjects using nested jsonb extract statements in the Select clause
(SELECT
ii.id,
ii.jsonb #>> '{hrid}' AS instance_hrid, ---- first level text extract
ii.jsonb #>> '{title}' AS title, ---- first level text extract
jsonb_extract_path_text (jsonb_array_elements (jsonb_extract_path (ii.jsonb, 'contributors')),'name') AS contributors, -- contributors array extract (second-level extract)
(jsonb_array_elements (jsonb_extract_path (ii.jsonb,'languages'))) #>>'{}' as languages, ----- languages array extract (top-level extract) – note the empty curly brackets
jsonb_extract_path_text (jsonb_array_elements (jsonb_extract_path (ii.jsonb, 'subjects')),'value') as subjects ----- subject array extract (second-level extract)
...