Page History

...

In the example below, 'lastName' and 'firstName' are both "second-level" fields found under "personal." 

SELECT
     users.id AS user_id,
     jsonb_extract_path_text (users.jsonb, 'personal', 'lastName') AS requestor_last_name,
     jsonb_extract_path_text (users.jsonb, 'personal', 'firstName') AS requestor_first_name
FROM
     folio_users.users

The following expression will also get "lastName" and "firstName":

SELECT
     users.id AS user_id,
     users.jsonb #>> '{personal, lastName}' AS requestor_last_name,
     users.jsonb #>> '{personal, firstName}' AS requestor_first_name
FROM
     folio_users.users

Extracting "Third level" Data Fields

...

This is the code to extract them. Note that the fields listed within the parentheses must be in the same hierarchical order as they appear in the json data.

SELECT
     audit_loan.id AS audit_loan_id,
     jsonb_extract_path_text (audit_loan.jsonb, 'loan', 'status', 'name') AS loan_status_name,
     jsonb_extract_path_text (audit_loan.jsonb, 'loan', 'metadata', 'updatedByUserId') AS loan_updated_by_user_id
FROM
     folio_circulation.audit_loan

The following expression will also get the third-level values:

SELECT
     audit_loan.id AS audit_loan_id,
     audit_loan.jsonb #>> '{loan, status, name}' AS loan_status__name,
     audit_loan.jsonb #>> '{loan, metadata, updatedByUserId}' AS loan_updated_by_user_id
FROM
     folio_circulation.audit_loan

...

In the previous examples, the elements being extracted were "text objects" within the table's JSON array, and were denoted by curly brackets { }. In this next example, we need to extract multiple values from an "array" object (denoted by square brackets [ ] ) that is embedded in a hierarchy of text elements. This calls for nested json extract statements, a structure very similar to what we used in Access with nested "GetField" and "GetXXXBLOB" functions.

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.

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.

• Nested extract statements are applied from the outside in. So the left-most json statement applies to the right-most element in the parentheses
• The entire nested extract statement is cast as a "jsonb" (created by the cross join) is a json object
• That jsonb json object is then used in the select stanza to get the needed fields within it (by using another json extract statement on it)
• Note that SQL performs the work in the FROM stanza before it performs the work in the SELECT stanza; that's how it knows about the json object you created through the cross join

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:

...