Page History

...

-- 4. Alternative to using cross joins

This method captures Cross join queries will return only those records that have all the array elements you are trying to find. (Note that it is possible to use multiple cross joins in the FROM stanza - 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 extrat statements. 

The following method captures the array elements by using nested json and other json elements) by using nested extract statements in the Select stanza. In this example, we're finding three array 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 the selected array elements will drop out. 

WITH recs AS

(SELECT

ii.id,

...

(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)

...

(ii.jsonb #>> '{hrid}')::INT >=313839  ---- I'm using a hrid range , or the query will take a very long time to runhere for illustration purposes

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

)

SELECTSELECT  -------- this step aggregates the results of the first query

recs.id,

recs.instance_hrid,

...