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. 

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").

...

The example below uses the folio_users.custom_fields table, which has an embedded array called "values" containg three fields: "id","value" and "default".  The "Values"array is embedded within two preceding text objects:

...

This illustrates three methods for extracting an embedded array from the folio_users.custom_fields table (example)

-- 1. No cross join -- just use triple-nested json extract statements in the Select clause

...