ABCDEFGHIJKLMNOPQRSTUVWXY
1
2
3
These notes have moved to:
https://github.com/folio-org/folio-analytics/blob/main/sql_metadb/porting.md
4
5
6
DescriptionLDP 1.xMetadb v0.11.0-beta2 (current)Metadb - planned
7
Show LDP/Metadb versionSELECT version FROM metadb.init;
8
Select JSON dataSELECT data FROM user_groups;SELECT jsonb FROM folio_users.groups;SELECT jsonb FROM folio_users.groups;
9
Select formatted JSON dataSELECT data FROM user_groups;SELECT jsonb_pretty(jsonb) FROM folio_users.groups;SELECT jsonb_pretty(jsonb) FROM folio_users.groups;
10
Select JSON fieldSELECT json_extract_path_text(data, 'group')
FROM user_groups;		SELECT jsonb_extract_path_text(jsonb, 'group')
FROM folio_users.groups;		SELECT jsonb_extract_path_text(jsonb, 'group')
FROM folio_users.groups;
-- (or) ---------------------------------------
SELECT jsonb->>'group' FROM folio_users.groups;
11
Select history dataSELECT id, updated, data
FROM history.user_groups
ORDER BY 1, 2;		SELECT id, __start, __end, jsonb_pretty(jsonb)
FROM folio_users.groups__
ORDER BY 1, 2;		SELECT id, __start, __end, jsonb_pretty(jsonb)
FROM folio_users.groups__
ORDER BY 1, 2;
12
Select first level JSON field from columnSELECT expiration_offset_in_days FROM user_groups;SELECT expiration_offset_in_days FROM folio_users.groups__t;
13
Select JSON array data from columnsSELECT *
FROM folio_reporting.item_notes
WHERE item_id = '4428a37c-8bae-4f0d-865d-970d83d5ad55'
ORDER BY note_ordinality		SELECT *
FROM folio_inventory.item__t__notes
WHERE id = '4428a37c-8bae-4f0d-865d-970d83d5ad55'
ORDER BY notes__o;
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