Extracting Fields from Metadb JSON arrays

Extracting "First Level" Data Fields 

In the code below, 'item_status' is a "first level" jsonb field. In the JSON data array in this table, the main field 'status' has only one subfield (one possible value). The data field is extracted from the folio_users.users__ table because this table contains JSON data array where that field exists (this field is not available in the users__t "transformed" table). Note that the folio_users.users__ table has 2 underscores at the end of its name.

SELECT
     folio_users.users__.id,
     jsonb_extract_path_text (folio_users.users__ .jsonb, 'status') AS item_status,
FROM folio_users.users__

Extracting "Second Level" Data Fields

In the code below, 'requester_last_name' and 'requester_first_name' are both "second-level" subfields in the JSON data array of the folio_users.users__ table. They are found under the "first level" field called "personal." The "personal" field contains many additional "second-level" subfields in the JSON data array (e.g., first_name, middle_name, last_name, id, etc.). Note that the folio_users.users__ table has 2 underscores at the end of its name.

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

Extracting "Third level" Data Fields and embedded arrays

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

• 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 nested extract statement is cast as a "jsonb" object
• That jsonb object is then used in the select stanza to get the needed fields (by using another json extract statment on it)

The example below uses the folio_users.custom_fields table, which has embedded array elements ("id","value" and "default") nested within a hierarchy of objects: 

• "selectField" (object)
• "options" (object)
• "values" (array). – "Values" is the array that contains fields we need

Here is what the custom_fields data array looks like:

We want to get the elements under the "values" array (denoted by square brackets):

The code to do that is:

SELECT

   cft.id as custom_fields_id,

   cft.ref_id,

   cft.name AS custom_fields_name,

   jsonb_extract_path_text (values.jsonb,'id') as value_id,

   jsonb_extract_path_text (values.jsonb,'value') as value_name

           ------ because "values" is a jsonb object (produced by the cross join below), I need to use another json extract statement in "Select" to get the value_id and the value_name

FROM folio_users.custom_fields AS cf

CROSS JOIN LATERAL jsonb_array_elements (jsonb_extract_path (cf.jsonb, 'selectField', 'options', 'values')) AS values (jsonb)
----- "jsonb_array_elements" works on the 'values' element in parentheses; it specifies that "values" is an array
----- "jsonb_extract_path" works on the "selectField" and "options" objects – the order of objects listed must be the same hierarchical order as they appear in the table
----- the entire cross join statement produces a jsonb object names "values" (but you can name this object whatever you like)

LEFT JOIN folio_users.custom_fields__t AS cft  ------ I'm joining to the custom_fields__t table to show the custom fields id, the ref_id and the custom_fields name (this is not necessary, it's just a convenience)

ON jsonb_extract_path_text (cf.jsonb,'id')::uuid = cft.id

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