Page History

...

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

• 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 (created by the cross join) is a json object
• That 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, and why you can use that object in the Select stanza

...

ON (depts.jsonb #>> '{}')::UUID = ud.id-------- note that I'm using the shortcut expression to join to the id in the departments__t table

RESULT:

3. Extracting Arrays - Summary: Three ways to extract arrays

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

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

SELECT

cft.id AS custom_fields_id,

cft.ref_id,

cft.name AS custom_fields_name,

jsonb_extract_path_text (jsonb_array_elements (jsonb_extract_path (cf.jsonb, 'selectField', 'options', 'values')),'id') AS value_id,

jsonb_extract_path_text (jsonb_array_elements (jsonb_extract_path (cf.jsonb, 'selectField', 'options', 'values')),'value') AS value_name

FROM folio_users.custom_fields AS cf

LEFT JOIN folio_users.custom_fields__t AS cft

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

ORDER BY name, value_id, value_name

;

-- 2. Use a cross join and put the result in a single-level json extract statement in the Select clause

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

FROM folio_users.custom_fields AS cf

CROSS JOIN LATERAL

jsonb_array_elements (jsonb_extract_path (cf.jsonb, 'selectField', 'options', 'values'))

AS values (jsonb)

LEFT JOIN folio_users.custom_fields__t AS cft

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')

;

-- 3. Use a cross join but use the shortcut method in the Select clause to get the values in the array

SELECT

cft.id AS custom_fields_id,

cft.ref_id,

cft.name AS custom_fields_name,

values.jsonb #>> '{id}' AS value_id,

values.jsonb #>> '{value}' AS 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)

LEFT JOIN folio_users.custom_fields__t AS cft

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')

;