ABCDEFGHIJKLMNOPQRSTUVWXYZ
1
Extracting data arrays cheat sheet - 3-6-26
2
3
Table (example)fieldsHow to extractAlternate Method
4
folio_inventory.item"id": "82000dbe-073c-427d-a8a9-19fc72f1114d"item.jsonb#>>'{id}'jsonb_extract_path_text (item.jsonb, 'id')::UUID
5
"hrid": "1553160"item.jsonb#>>'{hrid}'jsonb_extract_path_text (item.jsonb, 'hrid')
6
7
folio_inventory.item "status": {
"date": "2002-07-23T19:10:13.000+00:00",
"name": "Available"
}		item.jsonb#>>'{status,date}'::DATE
item.jsonb#>>'{status,name}'		jsonb_extract_path_text (item.jsonb, 'status', 'date')::DATE
jsonb_extract_path_text (item.jsonb, 'status', 'name')
8
9
folio_inventory.item "notes": [
{
"note": "/repositories/4/top_containers/119051",
"staffOnly": true,
"itemNoteTypeId": "c66b92e3-3eaf-491a-9d18-7b4d495e842d"
},
{
"note": "415 none-MF11-A--2",
"staffOnly": true,
"itemNoteTypeId": "ea208f79-cb1f-4449-8d10-a069da4c8ae1"
}
],		jsonb_extract_path_text (jsonb_array_elements (jsonb_extract_path (item.jsonb, 'notes')),'note')

jsonb_extract_path_text (jsonb_array_elements (jsonb_extract_path (item.jsonb, 'notes')),'staffOnly')

jsonb_extract_path_text (jsonb_array_elements (jsonb_extract_path (item.jsonb, 'notes')),'itemNoteTypeId')		Use a cross join in the FROM section:

SELECT (...various fields)
FROM folio_inventory.item
cross join lateral jsonb_array_elements (jsonb_extract_path (item.jsonb,'notes')) AS note_extract (jsonb)

This cross join makes a new jsonb object (here called "note_extract" but you can call it anything).
Then use the "note_extract" object in the Select clause to extract the elements from it:

SELECT
note_extract.jsonb#>>'{note}' AS item_note,
note_extract.jsonb#>>'{staffOnly}'::BOOLEAN AS staff_only_yes_or_no,
note_extract.jsonb#>>'{itemNoteTypeId}'::UUID AS note_type_id
... etc.
FROM folio_inventory.item
cross join lateral jsonb_array_elements (jsonb_extract_path (item.jsonb,'notes')) AS note_extract (jsonb)
10
11
folio_inventory.item"yearCaption": [], jsonb_array_elements (jsonb_extract_path (item.jsonb,'yearCaption')) #>>'{}'
12
13
folio_users.custom_fields "selectField": {
"options": {
"values": [
{
"id": "opt_0",
"value": "Active",
"default": true
},
{
"id": "opt_1",
"value": "Inactive",
"default": false
}
],
"sortingOrder": "CUSTOM"
},
"multiSelect": false
}		To extract the "values" section:

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,

jsonb_extract_path_text (jsonb_array_elements (jsonb_extract_path (cf.jsonb, 'selectField', 'options', 'values')),'default')::BOOLEAN as default_yes_or_no		Use a cross join in the FROM section, and use the created json object (here called "values_extract") to get the fields from the values array:

SELECT
values_extract.jsonb #>> '{id}'::UUID AS value_id,
values_extract.jsonb #>> '{value}' AS value_name
values_extract.jsonb#>>'{default}'::BOOLEAN AS default_yes_or_no

FROM folio_users.custom_fields AS cf
cross join lateral jsonb_array_elements (jsonb_extract_path (cf.jsonb, 'selectField', 'options', 'values')) as values_extract (jsonb)
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100