Page History

...

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

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

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

Extracting Arrays 

-- 1. Arrays embedded in text objects

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.

...

RESULT:

Extracting Arrays 

-- 2. Array is at the top-most hierarchical level in a table

Sometimes you want to extract an array that is at the top-most hierarchical level of a table – that is, it is not embedded within any other object. An example of this is "departments" element in the folio_users.users table:

...

      depts.ordinalityasdept_ordinality   ------ this displays the ordinality of the departments departments (the sequence of occurence in the record)

FROM folio_users.users

      CROSS JOIN LATERAL  jsonb_array_elements (jsonb_extract_path (users.jsonb,'departments'))

...