Page History

...

ORDER BY name, jsonb_extract_path_text (values.jsonb,'id'), jsonb_extract_path_text (values.jsonb,'value')

4. Extracting Arrays - Alternative to using cross joins, and how to prevent records without the array elements from dropping out

Cross join queries will return only those records that have all the array elements you are trying to find. (Note that it is It's possible to use multiple cross joins join statements in the FROM stanza to get multiple array values (for example, subjects, contributors and languages from the instance table) - but the results will show only those records that have ALL the array elements specified in the cross joins. If a record is missing even a single specified element, it will drop out.) Because of this, you either need to do separate cross-join queries for each needed element, and left-join them together to a list of all records; or else use this method that employs nested json extract statements.  

If you want to find records that have any or all of the particular array fields, AND show all the records that don't have any of those array fields, the better plan is to do a 2-part query.

• • 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  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 records

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

-- Get the json jsonb extracts in the first query , then aggregate them in the second query. using nested jsonb extract statements in the Select clause 

WITH recs AS

(SELECT

ii.id,

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

...

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

FROM folio_inventory.instance AS ii

WHERE

(ii.jsonb #>> '{hrid}')::INT >=313839  ---- I'm using a hrid range here for illustration purposes

AND (ii.jsonb #>> '{hrid}')::INT <313900

.instance AS ii

),)

SELECT  -------- this step aggregates the results of the first queryget 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

recs.id,

recs.instance_hrid,

recs.title,

string_agg (distinct recs.contributors, ' | ') AS as contributors,_aggregated

string_agg (distinct recs.languageslanuages, ' | ') AS  languages aggregated,

string_agg (distinct recs.subjects, ' | ') AS as subjects_aggregated

FROM recs

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

ORDER BY instance_hrid::INT

;

RESULT:

Image RemovedImage Added