Page History

...

• jsonb_extract_path   ---- this expression specifies the PATH necessary to get the last field entered in the path; the result is a json object and will display in double quotes
• jsonb_extract_path_text   ---- this expression does the same thing as jsonb_extract_path, but returns a TEXT object (displays without quotes)
• jsonb_array_elements   ---- this expression is used for extracting values within an array object
• the operator #>>. The format for using this operator is: table_name.jsonb #>> '{path_of_fields_in_the_hierarchy_separated_by_commas}' 
  • Example: audit_loan.jsonb #>> '{loan, metadata, updatedByUserId}'  -- this will get the value for "updatedByUserId" (the result is in Text format)

Extracting "First Level" Data Fields 

...

CROSS JOIN LATERAL jsonb_array_elements (jsonb_extract_path (cf.jsonb, 'selectField', 'options', 'values')) AS values (jsonb)
----- "jsonb_array_elements" works on the 'values' array object at the end of the parentheses; we use this extract expression because "values" is an array
----- "jsonb_extract_path" works on the "selectField" and "options" objects looks for the path of elements to follow in order to get to the values array – the objects listed must be in the same hierarchical order as they appear in the table
----- the entire cross join statement produces a jsonb object which I named "values" (note: what you name this object is totally arbitrary - you could call it "stuff" if you felt like it – "stuff (jsonb)")

...