3 * Test jsonb delete and concatenate operator functions for 9.4
5 * Portions Copyright (c) 1996-2015, PostgreSQL Global Development Group
6 * Portions Copyright (c) 1994, Regents of the University of California
7 * Author: Glyn Astill <glyn@8kb.co.uk>
9 * This is purely experimentation and will contain many errors and bad form
10 * DO NOT USE ON PRODUCTION SYSTEMS.
16 #include "utils/array.h"
17 #include "utils/jsonb.h"
18 #include "catalog/pg_type.h"
19 #include "utils/builtins.h"
20 #include "jsonb_opx.h"
22 #ifdef PG_MODULE_MAGIC
26 Datum jsonb_delete_key(PG_FUNCTION_ARGS);
28 PG_FUNCTION_INFO_V1(jsonb_delete_key);
31 * Operator function to delete key from left operand where a match is found in
34 * jsonb, text -> jsonb
38 jsonb_delete_key(PG_FUNCTION_ARGS)
40 /* pointers to incoming jsonb and text data */
41 Jsonb *input_jsonb = PG_GETARG_JSONB(0);
42 text *input_text = PG_GETARG_TEXT_P(1);
44 /* pointers to return jsonb value data and state to be converted to jsonb on return */
45 JsonbParseState *state = NULL;
46 JsonbValue *return_jsonb_value = NULL;
48 /* pointer to iterator for input_jsonb value data */
49 JsonbIterator *jsonb_iterator;
50 JsonbValue jsonb_iterator_value;
51 int32 jsonb_iterator_token;
53 /* variables used for skip logic */
56 int32 array_level = 0;
60 * If we've been supplied with an existing key iterate round json data and rebuild
61 * with key/element excluded.
63 * skip_key, nest_level and array_level are crude counts to check if the the value
64 * for the key is closed and ensure we don't match on keys within nested objects.
65 * Because we are recursing into nested elements but blindly just pushing them onto
66 * the return value we can get away without deeper knowledge of the json?
69 jsonb_iterator = JsonbIteratorInit(&input_jsonb->root);
71 while ((jsonb_iterator_token = JsonbIteratorNext(&jsonb_iterator, &jsonb_iterator_value, false)) != WJB_DONE)
74 switch (jsonb_iterator_token)
79 case WJB_BEGIN_OBJECT:
84 if (skip_level == 0 && ((jsonb_iterator_token == WJB_KEY && nest_level == 1 && array_level == 0) ||
85 (jsonb_iterator_token == WJB_ELEM && nest_level == 0 && array_level == 1)))
87 if (jsonb_iterator_value.type == jbvString)
89 if ((jsonb_iterator_value.val.string.len == VARSIZE_ANY_EXHDR(input_text)) &&
90 (memcmp(jsonb_iterator_value.val.string.val,
91 VARDATA_ANY(input_text),
92 jsonb_iterator_value.val.string.len) == 0))
95 if (jsonb_iterator_token == WJB_ELEM)
98 skip_level = nest_level;
104 if (push && (skip_level == 0 || nest_level < skip_level))
106 return_jsonb_value = pushJsonbValueBlind(&state, jsonb_iterator_token, &jsonb_iterator_value);
109 switch (jsonb_iterator_token)
113 if (skip_level == nest_level && array_level == 0)
118 if (skip_level == nest_level && array_level == 0)
122 if (skip_level == nest_level)
127 if (JB_ROOT_IS_SCALAR(input_jsonb) && !return_jsonb_value->val.array.rawScalar && return_jsonb_value->val.array.nElems == 1)
128 return_jsonb_value->val.array.rawScalar = true;
130 PG_FREE_IF_COPY(input_jsonb, 0);
131 PG_FREE_IF_COPY(input_text, 1);
133 PG_RETURN_JSONB(JsonbValueToJsonb(return_jsonb_value));
136 Datum jsonb_delete_keys(PG_FUNCTION_ARGS);
138 PG_FUNCTION_INFO_V1(jsonb_delete_keys);
141 * Operator function to delete keys from left operand where a match is found in
144 * jsonb, text[] -> jsonb
148 jsonb_delete_keys(PG_FUNCTION_ARGS)
153 /* pointers to incoming jsonb and text[] data */
154 Jsonb *input_jsonb = PG_GETARG_JSONB(0);
155 ArrayType *input_array = PG_GETARG_ARRAYTYPE_P(1);
157 /* pointers to return jsonb value data and state to be converted to jsonb on return */
158 JsonbParseState *state = NULL;
159 JsonbValue *return_jsonb_value = NULL;
161 /* pointer to iterator for input_jsonb value data */
162 JsonbIterator *jsonb_iterator;
163 JsonbValue jsonb_iterator_value;
164 int32 jsonb_iterator_token;
166 /* variables used for skip logic */
167 int32 skip_level = 0;
168 int32 nest_level = 0;
169 int32 array_level = 0;
172 /* array element variables for use during deconstruction */
177 /* individual array values values from incoming text[] */
178 text *array_element_text;
180 /* assert input_array is a text array type */
181 Assert(ARR_ELEMTYPE(input_array) == TEXTOID);
183 /* check input_array is one-dimensional */
184 if (ARR_NDIM(input_array) > 1)
186 (errcode(ERRCODE_ARRAY_SUBSCRIPT_ERROR),
187 errmsg("1 dimensional text array expected")));
189 /* deconstruct array elements */
190 deconstruct_array(input_array, TEXTOID, -1, false, 'i',
191 &datums, &nulls, &count);
193 /* if the array is empty there's no work to do so return the input value */
195 PG_RETURN_JSONB(input_jsonb);
198 * If we've been supplied with existing keys iterate round json data and rebuild
199 * with keys/elements excluded.
201 * skip_level, nest_level and array_level are crude counts to check if the the value
202 * for the key is closed and ensure we don't match on keys within nested objects.
203 * Because we are recursing into nested elements but blindly just pushing them onto
204 * the return value we can get away without deeper knowledge of the json?
206 jsonb_iterator = JsonbIteratorInit(&input_jsonb->root);
208 while ((jsonb_iterator_token = JsonbIteratorNext(&jsonb_iterator, &jsonb_iterator_value, false)) != WJB_DONE) {
211 switch (jsonb_iterator_token)
213 case WJB_BEGIN_ARRAY:
216 case WJB_BEGIN_OBJECT:
221 if (skip_level == 0 && ((jsonb_iterator_token == WJB_KEY && nest_level == 1 && array_level == 0) ||
222 (jsonb_iterator_token == WJB_ELEM && nest_level == 0 && array_level == 1)))
224 if (jsonb_iterator_value.type == jbvString || jsonb_iterator_value.type == jbvNull)
226 for (i=0; i<count; i++)
229 array_element_text = DatumGetTextP(datums[i]);
231 array_element_text = NULL;
233 if (((array_element_text != NULL) && (jsonb_iterator_value.val.string.len == VARSIZE_ANY_EXHDR(array_element_text)) &&
234 (memcmp(jsonb_iterator_value.val.string.val,
235 VARDATA_ANY(array_element_text),
236 jsonb_iterator_value.val.string.len) == 0)) || ((array_element_text == NULL) && (jsonb_iterator_value.type == jbvNull)))
238 if (jsonb_iterator_token == WJB_ELEM)
241 skip_level = nest_level;
249 if (push && (skip_level == 0 || nest_level < skip_level))
251 return_jsonb_value = pushJsonbValueBlind(&state, jsonb_iterator_token, &jsonb_iterator_value);
254 switch (jsonb_iterator_token)
258 if (skip_level == nest_level && array_level == 0)
263 if (skip_level == nest_level && array_level == 0)
267 if (skip_level == nest_level)
272 if (JB_ROOT_IS_SCALAR(input_jsonb) && !return_jsonb_value->val.array.rawScalar && return_jsonb_value->val.array.nElems == 1)
273 return_jsonb_value->val.array.rawScalar = true;
275 PG_FREE_IF_COPY(input_jsonb, 0);
276 PG_FREE_IF_COPY(input_array, 1);
278 PG_RETURN_JSONB(JsonbValueToJsonb(return_jsonb_value));
281 Datum jsonb_delete_jsonb(PG_FUNCTION_ARGS);
283 PG_FUNCTION_INFO_V1(jsonb_delete_jsonb);
286 * Operator function to delete keys and values from left operand where a match
287 * is found in the right operand.
289 * jsonb, jsonb -> jsonb
293 jsonb_delete_jsonb(PG_FUNCTION_ARGS)
295 /* pointers to incoming jsonb and text[] data */
296 Jsonb *input_jsonb_a = PG_GETARG_JSONB(0);
297 Jsonb *input_jsonb_b = PG_GETARG_JSONB(1);
299 /* pointers to return jsonb value data and state to be converted to jsonb on return */
300 JsonbValue *return_jsonb_value = NULL;
301 JsonbParseState *state = NULL;
303 /* pointer to iterator for input_jsonb_a and temporary value data */
304 JsonbIterator *jsonb_iterator;
305 JsonbValue jsonb_iterator_value;
306 JsonbValue jsonb_iterator_key;
307 int32 jsonb_iterator_token;
308 bool skip_nested = false;
312 /* pointer to lookup on input_jsonb_b */
313 JsonbValue *jsonb_lookup_value = NULL;
316 * check if either right jsonb is empty and return left if so
318 if (JB_ROOT_COUNT(input_jsonb_b) == 0)
319 PG_RETURN_JSONB(input_jsonb_a);
321 jsonb_iterator = JsonbIteratorInit(&input_jsonb_a->root);
323 while ((jsonb_iterator_token = JsonbIteratorNext(&jsonb_iterator, &jsonb_iterator_value, skip_nested)) != WJB_DONE)
328 switch (jsonb_iterator_token)
330 case WJB_BEGIN_ARRAY:
331 case WJB_BEGIN_OBJECT:
334 return_jsonb_value = pushJsonbValue(&state, jsonb_iterator_token, NULL);
338 * findJsonbValueFromContainer only supports jsonb arrays containting scalar values?
339 * If container is something like '[[1]]' or '[{"a":1}]' will error with "invalid jsonb scalar type"
341 jsonb_lookup_value = findJsonbValueFromContainer(&input_jsonb_b->root, JB_FOBJECT | JB_FARRAY, &jsonb_iterator_value);
342 if (jsonb_lookup_value == NULL)
344 if (jsonb_iterator_value.type == jbvBinary)
346 return_jsonb_value = pushJsonbBinary(&state, jsonb_iterator_value.val.binary.data);
350 return_jsonb_value = pushJsonbValue(&state, WJB_ELEM, &jsonb_iterator_value);
355 jsonb_lookup_value = findJsonbValueFromContainer(&input_jsonb_b->root, JB_FOBJECT | JB_FARRAY, &jsonb_iterator_value);
357 jsonb_iterator_key = jsonb_iterator_value;
358 jsonb_iterator_token = JsonbIteratorNext(&jsonb_iterator, &jsonb_iterator_value, skip_nested);
359 if (jsonb_iterator_token != WJB_VALUE)
360 elog(ERROR, "invalid JsonbIteratorNext (expected WJB_VALUE) rc: %d", jsonb_iterator_token);
362 if (jsonb_lookup_value != NULL)
365 if (jsonb_lookup_value->type == jsonb_iterator_value.type)
367 switch (jsonb_lookup_value->type)
373 if (DatumGetBool(DirectFunctionCall2(numeric_eq,
374 PointerGetDatum(jsonb_lookup_value->val.numeric),
375 PointerGetDatum(jsonb_iterator_value.val.numeric))))
379 if ((jsonb_lookup_value->val.string.len == jsonb_iterator_value.val.string.len) &&
380 (memcmp(jsonb_lookup_value->val.string.val,
381 jsonb_iterator_value.val.string.val,
382 jsonb_lookup_value->val.string.len) == 0))
386 if ((jsonb_lookup_value->val.binary.len == jsonb_iterator_value.val.binary.len) &&
387 (memcmp(jsonb_lookup_value->val.binary.data,
388 jsonb_iterator_value.val.binary.data,
389 jsonb_lookup_value->val.binary.len) == 0))
393 if (jsonb_lookup_value->val.boolean == jsonb_iterator_value.val.boolean)
397 /* should not be possible? */
399 /* should not be possible? */
401 ereport(ERROR, (errcode(ERRCODE_SUCCESSFUL_COMPLETION), errmsg("unexpected lookup type %i", jsonb_iterator_token)));
408 return_jsonb_value = pushJsonbValue(&state, WJB_KEY, &jsonb_iterator_key);
410 /* if our value is nested binary data, iterate separately pushing each val */
411 if (jsonb_iterator_value.type == jbvBinary)
413 return_jsonb_value = pushJsonbBinary(&state, jsonb_iterator_value.val.binary.data);
417 return_jsonb_value = pushJsonbValue(&state, jsonb_iterator_token, &jsonb_iterator_value);
422 /* should not be possible */
424 elog(ERROR, "invalid JsonbIteratorNext rc: %d", jsonb_iterator_token);
429 if (JB_ROOT_IS_SCALAR(input_jsonb_a) && !return_jsonb_value->val.array.rawScalar && return_jsonb_value->val.array.nElems == 1)
430 return_jsonb_value->val.array.rawScalar = true;
432 PG_FREE_IF_COPY(input_jsonb_a, 0);
433 PG_FREE_IF_COPY(input_jsonb_b, 1);
435 PG_RETURN_JSONB(JsonbValueToJsonb(return_jsonb_value));
438 Datum jsonb_delete_path(PG_FUNCTION_ARGS);
440 PG_FUNCTION_INFO_V1(jsonb_delete_path);
444 * jsonb, text[] -> jsonb
448 jsonb_delete_path(PG_FUNCTION_ARGS)
450 /* pointers to incoming jsonb and text[] data */
451 Jsonb *input_jsonb_a = PG_GETARG_JSONB(0);
452 ArrayType *input_array = PG_GETARG_ARRAYTYPE_P(1);
454 /* pointer to return jsonb data */
455 Jsonb *return_jsonb = NULL;
457 return_jsonb = jsonbModifyPath(input_jsonb_a, input_array, NULL);
459 PG_FREE_IF_COPY(input_jsonb_a, 0);
460 PG_FREE_IF_COPY(input_array, 1);
462 PG_RETURN_JSONB(return_jsonb);
465 Datum jsonb_concat_jsonb(PG_FUNCTION_ARGS);
467 PG_FUNCTION_INFO_V1(jsonb_concat_jsonb);
470 * Operator function to concatenate json from left operand where a match
471 * is found in the right operand.
473 * jsonb, jsonb -> jsonb
477 jsonb_concat_jsonb(PG_FUNCTION_ARGS)
479 /* incoming jsonb data */
480 Jsonb *input_jsonb_a = PG_GETARG_JSONB(0);
481 Jsonb *input_jsonb_b = PG_GETARG_JSONB(1);
483 /* return jsonb value data to be converted to jsonb on return */
484 JsonbParseState *state = NULL;
485 JsonbValue *return_jsonb_value = NULL;
487 /* iterator for input_jsonb_b */
488 JsonbIterator *jsonb_iterator;
489 JsonbValue jsonb_iterator_value;
490 int32 jsonb_iterator_token;
491 int32 jsonb_root_open;
492 int32 jsonb_root_close;
494 int32 nest_level = 0;
498 * check if either supplied jsonb is empty and return the other if so
500 if (JB_ROOT_COUNT(input_jsonb_a) == 0)
501 PG_RETURN_JSONB(input_jsonb_b);
502 else if (JB_ROOT_COUNT(input_jsonb_b) == 0)
503 PG_RETURN_JSONB(input_jsonb_a);
506 * rather than restrict concatenation to objects, allow any jsonb root
507 * but if one is an array use an array as the root container else
510 if (JB_ROOT_IS_ARRAY(input_jsonb_a) || JB_ROOT_IS_ARRAY(input_jsonb_b))
512 jsonb_root_open = WJB_BEGIN_ARRAY;
513 jsonb_root_close = WJB_END_ARRAY;
516 jsonb_root_open = WJB_BEGIN_OBJECT;
517 jsonb_root_close = WJB_END_OBJECT;
521 * The following is essentially a cut 'n shut job; discarding the closing root
522 * object token from the first jsonb value and the opening one from the second.
523 * Values from each are just blindly pushed onto the return value leaving
524 * deduplication down to lower level jsonb logic.
527 return_jsonb_value = pushJsonbValue(&state, jsonb_root_open, NULL);
529 jsonb_iterator = JsonbIteratorInit(&input_jsonb_a->root);
531 while ((jsonb_iterator_token = JsonbIteratorNext(&jsonb_iterator, &jsonb_iterator_value, false)) != WJB_DONE)
533 if (jsonb_iterator_token == jsonb_root_open && (first || nest_level > 0))
542 else if (jsonb_iterator_token == jsonb_root_close && nest_level > 0)
550 return_jsonb_value = pushJsonbValueBlind(&state, jsonb_iterator_token, &jsonb_iterator_value);
555 jsonb_iterator = JsonbIteratorInit(&input_jsonb_b->root);
557 while ((jsonb_iterator_token = JsonbIteratorNext(&jsonb_iterator, &jsonb_iterator_value, false)) != WJB_DONE)
559 if (jsonb_iterator_token == jsonb_root_open && (first || nest_level > 0))
568 else if (jsonb_iterator_token == jsonb_root_close && nest_level > 0)
576 return_jsonb_value = pushJsonbValueBlind(&state, jsonb_iterator_token, &jsonb_iterator_value);
579 return_jsonb_value = pushJsonbValue(&state, jsonb_root_close, NULL);
581 PG_FREE_IF_COPY(input_jsonb_a, 0);
582 PG_FREE_IF_COPY(input_jsonb_b, 1);
584 PG_RETURN_JSONB(JsonbValueToJsonb(return_jsonb_value));
587 Datum jsonb_replace_jsonb(PG_FUNCTION_ARGS);
589 PG_FUNCTION_INFO_V1(jsonb_replace_jsonb);
592 * Operator function to replace json in left operand where keys match
593 * in the right operand.
595 * jsonb, jsonb -> jsonb
599 jsonb_replace_jsonb(PG_FUNCTION_ARGS)
601 /* incoming jsonb data */
602 Jsonb *input_jsonb_a = PG_GETARG_JSONB(0);
603 Jsonb *input_jsonb_b = PG_GETARG_JSONB(1);
605 /* return jsonb value data to be converted to jsonb on return */
606 JsonbParseState *state = NULL;
607 JsonbValue *return_jsonb_value = NULL;
609 /* lookup jsonb value data */
610 JsonbValue jsonb_lookup_key;
611 JsonbValue *jsonb_lookup_value = NULL;
612 uint32 jsonb_lookup_flags;
614 /* iterator for input_jsonb_b */
615 JsonbIterator *jsonb_iterator;
616 JsonbValue jsonb_iterator_value;
617 int32 jsonb_iterator_token;
620 * check if supplied replacement jsonb is empty and return unchanged if so
622 if (JB_ROOT_COUNT(input_jsonb_b) == 0)
623 PG_RETURN_JSONB(input_jsonb_a);
625 if (JB_ROOT_IS_OBJECT(input_jsonb_a))
626 jsonb_lookup_flags = JB_FOBJECT;
628 jsonb_lookup_flags = JB_FOBJECT | JB_FARRAY;
630 jsonb_iterator = JsonbIteratorInit(&input_jsonb_a->root);
631 while ((jsonb_iterator_token = JsonbIteratorNext(&jsonb_iterator, &jsonb_iterator_value, true)) != WJB_DONE)
633 if ((jsonb_iterator_token == WJB_ELEM ) && (jsonb_iterator_value.type == jbvBinary))
635 return_jsonb_value = pushJsonbBinary(&state, jsonb_iterator_value.val.binary.data);
639 return_jsonb_value = pushJsonbValueBlind(&state, jsonb_iterator_token, &jsonb_iterator_value);
642 Assert(jsonb_iterator_token != WJB_VALUE);
644 if ( jsonb_iterator_token == WJB_KEY )
646 jsonb_lookup_key.type = jbvString;
647 jsonb_lookup_key.val.string.val = jsonb_iterator_value.val.string.val;
648 jsonb_lookup_key.val.string.len = jsonb_iterator_value.val.string.len;
650 jsonb_iterator_token = JsonbIteratorNext(&jsonb_iterator, &jsonb_iterator_value, true);
651 Assert(jsonb_iterator_token == WJB_VALUE);
653 jsonb_lookup_value = findJsonbValueFromContainer(&input_jsonb_b->root,
654 jsonb_lookup_flags, &jsonb_lookup_key);
656 /* if there's nothing to replace push the original value */
657 if (jsonb_lookup_value == NULL)
659 jsonb_lookup_value = &jsonb_iterator_value;
662 /* if our value is nested binary data, iterate separately pushing each val */
663 if (jsonb_lookup_value->type == jbvBinary)
665 return_jsonb_value = pushJsonbBinary(&state, jsonb_lookup_value->val.binary.data);
669 return_jsonb_value = pushJsonbValue(&state, WJB_VALUE, jsonb_lookup_value);
674 if (JB_ROOT_IS_SCALAR(input_jsonb_a) && !return_jsonb_value->val.array.rawScalar && return_jsonb_value->val.array.nElems == 1)
675 return_jsonb_value->val.array.rawScalar = true;
677 PG_FREE_IF_COPY(input_jsonb_a, 0);
678 PG_FREE_IF_COPY(input_jsonb_b, 1);
680 PG_RETURN_JSONB(JsonbValueToJsonb(return_jsonb_value));
683 Datum jsonb_replace_path(PG_FUNCTION_ARGS);
685 PG_FUNCTION_INFO_V1(jsonb_replace_path);
689 * jsonb, text[], jsonb -> jsonb
693 jsonb_replace_path(PG_FUNCTION_ARGS)
695 /* pointers to incoming jsonb and text[] data */
696 Jsonb *input_jsonb_a = PG_GETARG_JSONB(0);
697 ArrayType *input_array = PG_GETARG_ARRAYTYPE_P(1);
698 Jsonb *input_jsonb_b = PG_GETARG_JSONB(2);
700 /* pointer to return jsonb data */
701 Jsonb *return_jsonb = NULL;
703 return_jsonb = jsonbModifyPath(input_jsonb_a, input_array, input_jsonb_b);
705 PG_FREE_IF_COPY(input_jsonb_a, 0);
706 PG_FREE_IF_COPY(input_array, 1);
707 PG_FREE_IF_COPY(input_jsonb_b, 2);
709 PG_RETURN_JSONB(return_jsonb);