Page History

...

When extracting arrays embedded in text objects, it's usually best to use a "cross join lateral" statement in the FROM stanza. This cross join creates a "json object" that can then be further extracted in the Select stanza. Warning: the records retrieved through a cross join OR from using a nested json extract expression will only be those that have the array elements you're looking for; records without those elements will drop out.  See "Alternative to using cross joins" (that captures all the records) at end of this page.

It's also possible to NOT use a cross join at all, but to nest the json extract statements in one long expression in the Select stanza. However, you lose the ability to include the ordinality of the values extracted. See Summary at the end of this page ("Three ways to extract arrays").

...

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

-- 4. Alternative to using cross joins

This method captures the array elements (and other json elements) by using embedded extract statements in the Select stanza. In this example, we're using elements from the folio_inventory.instance table:

-- This query gets the json extracts in the first query, then aggregates them in the second query. We cannot use cross joins, because records without the extracted array elements will drop out. 

WITH recs AS

(SELECT

ii.id,

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

ii.jsonb #>> '{title}' AS title, ---- first level text object extract

jsonb_extract_path_text (jsonb_array_elements (jsonb_extract_path (ii.jsonb, 'contributors')),'name') AS contributors, -- contributors array extract (second-level array)

(jsonb_array_elements (jsonb_extract_path (ii.jsonb,'languages'))) #>>'{}' as languages, ----- languages array extract (top-level array) – note empty curly brackets

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

FROM folio_inventory.instance AS ii

WHERE

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

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

)

SELECT

recs.id,

recs.instance_hrid,

recs.title,

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

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

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

FROM recs

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

ORDER BY instance_hrid::INT

;

RESULT:

Image Added