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
  • Next, create a query that finds all records from the source table (for example, get all instance ids from the folio_inventory.instance table), and  and left-join the results of the first query (that has the extracted array values) as a left-join to the query that found all the source-table recordsto 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 jsonb extracts in the first query array elements for contributors, languages and subject using nested jsonb extract statements in the Select clause 
WITH recs ASclause

(SELECT

ii.id,

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

...

FROM folio_inventory.instance AS ii

),

SELECT  -------- this get Get all the instance records from the folio_inventory.instance table; use a left join to bring in the records with array values from the first query; aggregate the contributors, languages and subjects fields

...