Page History

...

• • First, create a query that has a nested jsonb extract statement (in the Select clause) for EACH of the arrays you're trying to extract (example below) – do not use cross joins
    • if you want to use cross joins, create one cross join query for each array field that you want to extract; don't combine all the extracts in one cross-join query
    • You will have to left-join each of the query results to the all-records query (created in the second step)
  • NextSecond, create a query that finds all records from the source table (for example, get all instance ids from the folio_inventory.instance table), and left-join the results of the first query (or queries) to it

In this example, we're finding three array elements from the folio_inventory.instance table: contributors, languages, and subjects, then joining the result to a complete set of instance records.

...

WITH recs

...

AS    ----- Get the array elements for contributors, languages and subject 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)

FROM FROM folio_inventory.instance AS AS ii
),

...

SELECT     ------- Get all the

...

records from the

...

instance__t table;

...

left join

...

the results of the first query

...

recsthat got the array elements
    instance__t.id,
recs.    instance__t.hrid,
recs    instance__t.title,
    string_agg (distinct recs.contributors,' | ') as contributors_aggregated,
    string_agg (distinct recs.lanuageslanguages,' | ') as languages_aggregated,
    string_agg (distinct recs.subjects,' | ') as subjects_aggregated

FROM recs

GROUP BY recs.id, recs.instance_hrid, recs.title

...

aggregated 

from folio_inventory.instance__t 
    left join recs
    on instance__t.id = recs.id
    
group by 
    instance__t.id,
    instance__t.hrid,
    instance__t.title
;

;

RESULT: