· 8 years ago · Dec 26, 2017, 02:56 PM
1postgres=# create table test (data json);
2CREATE TABLE
3postgres=# insert into test (data) values ('{"a":1,"b":2}');
4INSERT 0 1
5postgres=# select data->'a' from test where data->>'b' = '2';
6 ?column?
7----------
8 1
9(1 row)
10postgres=# update test set data->'a' = to_json(5) where data->>'b' = '2';
11ERROR: syntax error at or near "->"
12LINE 1: update test set data->'a' = to_json(5) where data->>'b' = '2...
13
14SELECT jsonb '{"a":1}' || jsonb '{"b":2}', -- will yield jsonb '{"a":1,"b":2}'
15 jsonb '["a",1]' || jsonb '["b",2]' -- will yield jsonb '["a",1,"b",2]'
16
17SELECT jsonb '{"a":1}' || jsonb_build_object('<key>', '<value>')
18
19SELECT jsonb_set('{"a":[null,{"b":[]}]}', '{a,1,b,0}', jsonb '{"c":3}')
20-- will yield jsonb '{"a":[null,{"b":[{"c":3}]}]}'
21
22jsonb_set(target jsonb,
23 path text[],
24 new_value jsonb,
25 create_missing boolean default true)
26
27SELECT jsonb_set('{"a":[null,{"b":[1,2]}]}', '{a,1,b,1000}', jsonb '3', true)
28-- will yield jsonb '{"a":[null,{"b":[1,2,3]}]}'
29
30SELECT jsonb_insert('{"a":[null,{"b":[1]}]}', '{a,1,b,0}', jsonb '2')
31-- will yield jsonb '{"a":[null,{"b":[2,1]}]}', and
32SELECT jsonb_insert('{"a":[null,{"b":[1]}]}', '{a,1,b,0}', jsonb '2', true)
33-- will yield jsonb '{"a":[null,{"b":[1,2]}]}'
34
35jsonb_insert(target jsonb,
36 path text[],
37 new_value jsonb,
38 insert_after boolean default false)
39
40SELECT jsonb_insert('{"a":[null,{"b":[1,2]}]}', '{a,1,b,-1}', jsonb '3', true)
41-- will yield jsonb '{"a":[null,{"b":[1,2,3]}]}', and
42
43SELECT jsonb_insert('{"a":[null,{"b":[1]}]}', '{a,1,c}', jsonb '[2]')
44-- will yield jsonb '{"a":[null,{"b":[1],"c":[2]}]}', but
45SELECT jsonb_insert('{"a":[null,{"b":[1]}]}', '{a,1,b}', jsonb '[2]')
46-- will raise SQLSTATE 22023 (invalid_parameter_value): cannot replace existing key
47
48SELECT jsonb '{"a":1,"b":2}' - 'a', -- will yield jsonb '{"b":2}'
49 jsonb '["a",1,"b",2]' - 1 -- will yield jsonb '["a","b",2]'
50
51SELECT '{"a":[null,{"b":[3.14]}]}' #- '{a,1,b,0}'
52-- will yield jsonb '{"a":[null,{"b":[]}]}'
53
54CREATE OR REPLACE FUNCTION "json_object_set_key"(
55 "json" json,
56 "key_to_set" TEXT,
57 "value_to_set" anyelement
58)
59 RETURNS json
60 LANGUAGE sql
61 IMMUTABLE
62 STRICT
63AS $function$
64SELECT concat('{', string_agg(to_json("key") || ':' || "value", ','), '}')::json
65 FROM (SELECT *
66 FROM json_each("json")
67 WHERE "key" <> "key_to_set"
68 UNION ALL
69 SELECT "key_to_set", to_json("value_to_set")) AS "fields"
70$function$;
71
72CREATE OR REPLACE FUNCTION "json_object_set_keys"(
73 "json" json,
74 "keys_to_set" TEXT[],
75 "values_to_set" anyarray
76)
77 RETURNS json
78 LANGUAGE sql
79 IMMUTABLE
80 STRICT
81AS $function$
82SELECT concat('{', string_agg(to_json("key") || ':' || "value", ','), '}')::json
83 FROM (SELECT *
84 FROM json_each("json")
85 WHERE "key" <> ALL ("keys_to_set")
86 UNION ALL
87 SELECT DISTINCT ON ("keys_to_set"["index"])
88 "keys_to_set"["index"],
89 CASE
90 WHEN "values_to_set"["index"] IS NULL THEN 'null'::json
91 ELSE to_json("values_to_set"["index"])
92 END
93 FROM generate_subscripts("keys_to_set", 1) AS "keys"("index")
94 JOIN generate_subscripts("values_to_set", 1) AS "values"("index")
95 USING ("index")) AS "fields"
96$function$;
97
98CREATE OR REPLACE FUNCTION "json_object_update_key"(
99 "json" json,
100 "key_to_set" TEXT,
101 "value_to_set" anyelement
102)
103 RETURNS json
104 LANGUAGE sql
105 IMMUTABLE
106 STRICT
107AS $function$
108SELECT CASE
109 WHEN ("json" -> "key_to_set") IS NULL THEN "json"
110 ELSE (SELECT concat('{', string_agg(to_json("key") || ':' || "value", ','), '}')
111 FROM (SELECT *
112 FROM json_each("json")
113 WHERE "key" <> "key_to_set"
114 UNION ALL
115 SELECT "key_to_set", to_json("value_to_set")) AS "fields")::json
116END
117$function$;
118
119CREATE OR REPLACE FUNCTION "json_object_set_path"(
120 "json" json,
121 "key_path" TEXT[],
122 "value_to_set" anyelement
123)
124 RETURNS json
125 LANGUAGE sql
126 IMMUTABLE
127 STRICT
128AS $function$
129SELECT CASE COALESCE(array_length("key_path", 1), 0)
130 WHEN 0 THEN to_json("value_to_set")
131 WHEN 1 THEN "json_object_set_key"("json", "key_path"[l], "value_to_set")
132 ELSE "json_object_set_key"(
133 "json",
134 "key_path"[l],
135 "json_object_set_path"(
136 COALESCE(NULLIF(("json" -> "key_path"[l])::text, 'null'), '{}')::json,
137 "key_path"[l+1:u],
138 "value_to_set"
139 )
140 )
141 END
142 FROM array_lower("key_path", 1) l,
143 array_upper("key_path", 1) u
144$function$;
145
146update objects set body=jsonb_set(body, '{name}', '"Mary"', true) where id=1;
147
148UPDATE test
149SET data = data - 'a' || '{"a":5}'
150WHERE data->>'b' = '2';
151
152UPDATE test
153SET data = jsonb_set(data, '{a}', '5'::jsonb);
154
155CREATE OR REPLACE FUNCTION "json_object_del_key"(
156 "json" json,
157 "key_to_del" TEXT
158)
159 RETURNS json
160 LANGUAGE sql
161 IMMUTABLE
162 STRICT
163AS $function$
164SELECT CASE
165 WHEN ("json" -> "key_to_del") IS NULL THEN "json"
166 ELSE (SELECT concat('{', string_agg(to_json("key") || ':' || "value", ','), '}')
167 FROM (SELECT *
168 FROM json_each("json")
169 WHERE "key" <> "key_to_del"
170 ) AS "fields")::json
171END
172$function$;
173
174CREATE OR REPLACE FUNCTION "json_object_del_path"(
175 "json" json,
176 "key_path" TEXT[]
177)
178 RETURNS json
179 LANGUAGE sql
180 IMMUTABLE
181 STRICT
182AS $function$
183SELECT CASE
184 WHEN ("json" -> "key_path"[l] ) IS NULL THEN "json"
185 ELSE
186 CASE COALESCE(array_length("key_path", 1), 0)
187 WHEN 0 THEN "json"
188 WHEN 1 THEN "json_object_del_key"("json", "key_path"[l])
189 ELSE "json_object_set_key"(
190 "json",
191 "key_path"[l],
192 "json_object_del_path"(
193 COALESCE(NULLIF(("json" -> "key_path"[l])::text, 'null'), '{}')::json,
194 "key_path"[l+1:u]
195 )
196 )
197 END
198 END
199 FROM array_lower("key_path", 1) l,
200 array_upper("key_path", 1) u
201$function$;
202
203s1=# SELECT json_object_del_key ('{"hello":[7,3,1],"foo":{"mofu":"fuwa", "moe":"kyun"}}',
204 'foo'),
205 json_object_del_path('{"hello":[7,3,1],"foo":{"mofu":"fuwa", "moe":"kyun"}}',
206 '{"foo","moe"}');
207
208 json_object_del_key | json_object_del_path
209---------------------+-----------------------------------------
210 {"hello":[7,3,1]} | {"hello":[7,3,1],"foo":{"mofu":"fuwa"}}
211
212create language plpython2u;
213
214create or replace function json_set(jdata jsonb, jpaths jsonb, jvalue jsonb) returns jsonb as $$
215import json
216
217a = json.loads(jdata)
218b = json.loads(jpaths)
219
220if a.__class__.__name__ != 'dict' and a.__class__.__name__ != 'list':
221 raise plpy.Error("The json data must be an object or a string.")
222
223if b.__class__.__name__ != 'list':
224 raise plpy.Error("The json path must be an array of paths to traverse.")
225
226c = a
227for i in range(0, len(b)):
228 p = b[i]
229 plpy.notice('p == ' + str(p))
230
231 if i == len(b) - 1:
232 c[p] = json.loads(jvalue)
233
234 else:
235 if p.__class__.__name__ == 'unicode':
236 plpy.notice("Traversing '" + p + "'")
237 if c.__class__.__name__ != 'dict':
238 raise plpy.Error(" The value here is not a dictionary.")
239 else:
240 c = c[p]
241
242 if p.__class__.__name__ == 'int':
243 plpy.notice("Traversing " + str(p))
244 if c.__class__.__name__ != 'list':
245 raise plpy.Error(" The value here is not a list.")
246 else:
247 c = c[p]
248
249 if c is None:
250 break
251
252return json.dumps(a)
253$$ language plpython2u ;
254
255create table jsonb_table (jsonb_column jsonb);
256insert into jsonb_table values
257('{"cars":["Jaguar", {"type":"Unknown","partsList":[12, 34, 56]}, "Atom"]}');
258
259select jsonb_column->'cars'->1->'partsList'->2, jsonb_column from jsonb_table;
260
261update jsonb_table
262set jsonb_column = json_set(jsonb_column, '["cars",1,"partsList",2]', '99');
263
264select jsonb_column->'cars'->1->'partsList'->2, jsonb_column from jsonb_table;
265
266UPDATE test
267SET data = data::jsonb - 'a' || '{"a":5}'::jsonb
268WHERE data->>'b' = '2'
269
270CREATE or REPLACE FUNCTION json_update(data json, key text, value json)
271returns json
272as $$
273from json import loads, dumps
274if key is None: return data
275js = loads(data)
276js[key] = value
277return dumps(js)
278$$ language plpython3u
279
280update test set data=json_update(data, 'a', to_json(5)) where data->>'b' = '2';
281
282CREATE EXTENSION IF NOT EXISTS plpythonu;
283CREATE LANGUAGE plpythonu;
284
285CREATE OR REPLACE FUNCTION json_update(data json, key text, value text)
286 RETURNS json
287 AS $$
288 import json
289 json_data = json.loads(data)
290 json_data[key] = value
291 return json.dumps(json_data, indent=4)
292 $$ LANGUAGE plpythonu;
293
294-- Check how JSON looks before updating
295
296SELECT json_update(content::json, 'CFRDiagnosis.mod_nbs', '1')
297FROM sc_server_centre_document WHERE record_id = 35 AND template = 'CFRDiagnosis';
298
299-- Once satisfied update JSON inplace
300
301UPDATE sc_server_centre_document SET content = json_update(content::json, 'CFRDiagnosis.mod_nbs', '1')
302WHERE record_id = 35 AND template = 'CFRDiagnosis';
303
304CREATE OR REPLACE FUNCTION jsonb_merge(left JSONB, right JSONB)
305RETURNS JSONB
306AS $$
307SELECT
308 CASE WHEN jsonb_typeof($1) = 'object' AND jsonb_typeof($2) = 'object' THEN
309 (SELECT json_object_agg(COALESCE(o.key, n.key), CASE WHEN n.key IS NOT NULL THEN n.value ELSE o.value END)::jsonb
310 FROM jsonb_each($1) o
311 FULL JOIN jsonb_each($2) n ON (n.key = o.key))
312 ELSE
313 (CASE WHEN jsonb_typeof($1) = 'array' THEN LEFT($1::text, -1) ELSE '['||$1::text END ||', '||
314 CASE WHEN jsonb_typeof($2) = 'array' THEN RIGHT($2::text, -1) ELSE $2::text||']' END)::jsonb
315 END
316$$ LANGUAGE sql IMMUTABLE STRICT;
317GRANT EXECUTE ON FUNCTION jsonb_merge(jsonb, jsonb) TO public;
318CREATE OPERATOR || ( LEFTARG = jsonb, RIGHTARG = jsonb, PROCEDURE = jsonb_merge );
319
320CREATE OR REPLACE FUNCTION jsonb_update(val1 JSONB,val2 JSONB)
321RETURNS JSONB AS $$
322DECLARE
323 result JSONB;
324 v RECORD;
325BEGIN
326 IF jsonb_typeof(val2) = 'null'
327 THEN
328 RETURN val1;
329 END IF;
330
331 result = val1;
332
333 FOR v IN SELECT key, value FROM jsonb_each(val2) LOOP
334
335 IF jsonb_typeof(val2->v.key) = 'object'
336 THEN
337 result = result || jsonb_build_object(v.key, jsonb_update(val1->v.key, val2->v.key));
338 ELSE
339 result = result || jsonb_build_object(v.key, v.value);
340 END IF;
341 END LOOP;
342
343 RETURN result;
344END;
345$$ LANGUAGE plpgsql;
346
347select jsonb_update('{"a":{"b":{"c":{"d":5,"dd":6},"cc":1}},"aaa":5}'::jsonb, '{"a":{"b":{"c":{"d":15}}},"aa":9}'::jsonb);
348 jsonb_update
349---------------------------------------------------------------------
350 {"a": {"b": {"c": {"d": 15, "dd": 6}, "cc": 1}}, "aa": 9, "aaa": 5}
351(1 row)
352
353UPDATE users SET counters = counters || CONCAT('{"bar":', COALESCE(counters->>'bar','0')::int + 1, '}')::jsonb WHERE id = 1;
354
355SELECT * FROM users;
356
357 id | counters
358----+------------
359 1 | {"bar": 1}