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 and lookup value data */
49 JsonbValue jsonb_lookup_key;
50 JsonbValue *jsonb_lookup_value = NULL;
51 JsonbIterator *jsonb_iterator;
52 JsonbValue jsonb_iterator_value;
53 int32 jsonb_iterator_token;
55 /* variables used for skip logic */
58 int32 array_level = 0;
61 * if we are not deaing with an array first check to make sure the key exists
62 * this is potentially just extra unwanted work
64 if (!JB_ROOT_IS_ARRAY(input_jsonb))
66 jsonb_lookup_key.type = jbvString;
67 jsonb_lookup_key.val.string.val = VARDATA_ANY(input_text);
68 jsonb_lookup_key.val.string.len = VARSIZE_ANY_EXHDR(input_text);
70 jsonb_lookup_value = findJsonbValueFromContainer(&input_jsonb->root,
71 JB_FOBJECT | JB_FARRAY, &jsonb_lookup_key);
73 if (jsonb_lookup_value == NULL)
74 PG_RETURN_JSONB(input_jsonb);
78 * If we've been supplied with an existing key iterate round json data and rebuild
79 * with key/element excluded.
81 * skip_key, nest_level and array_level are crude counts to check if the the value
82 * for the key is closed and ensure we don't match on keys within nested objects.
83 * Because we are recursing into nested elements but blindly just pushing them onto
84 * the return value we can get away without deeper knowledge of the json?
87 jsonb_iterator = JsonbIteratorInit(&input_jsonb->root);
89 while ((jsonb_iterator_token = JsonbIteratorNext(&jsonb_iterator, &jsonb_iterator_value, false)) != WJB_DONE)
91 switch (jsonb_iterator_token)
96 return_jsonb_value = pushJsonbValue(&state, WJB_BEGIN_ARRAY, &jsonb_iterator_value);
98 case WJB_BEGIN_OBJECT:
101 return_jsonb_value = pushJsonbValue(&state, WJB_BEGIN_OBJECT, &jsonb_iterator_value);
104 /* only match array elements if they are text */
105 if (skip_key == 0 && nest_level == 0 && array_level > 0)
107 if (jsonb_iterator_value.type == jbvString)
109 if ((jsonb_iterator_value.val.string.len == VARSIZE_ANY_EXHDR(input_text)) &&
110 (memcmp(jsonb_iterator_value.val.string.val,
111 VARDATA_ANY(input_text),
112 jsonb_iterator_value.val.string.len) == 0))
117 return_jsonb_value = pushJsonbValue(&state, WJB_ELEM, &jsonb_iterator_value);
120 /* Check each key against our array of keys */
125 else if (nest_level == 1 && array_level == 0)
127 if ((jsonb_iterator_value.val.string.len == VARSIZE_ANY_EXHDR(input_text)) &&
128 (memcmp(jsonb_iterator_value.val.string.val,
129 VARDATA_ANY(input_text),
130 jsonb_iterator_value.val.string.len) == 0))
137 return_jsonb_value = pushJsonbValue(&state, WJB_KEY, &jsonb_iterator_value);
141 return_jsonb_value = pushJsonbValue(&state, WJB_VALUE, &jsonb_iterator_value);
142 else if (skip_key > 0)
148 return_jsonb_value = pushJsonbValue(&state, WJB_END_ARRAY, NULL);
149 else if (skip_key > 0 && array_level == 0)
155 return_jsonb_value = pushJsonbValue(&state, WJB_END_OBJECT, NULL);
156 else if (skip_key > 0)
160 elog(ERROR, "invalid JsonbIteratorNext rc: %d", jsonb_iterator_token);
163 PG_FREE_IF_COPY(input_jsonb, 0);
164 PG_FREE_IF_COPY(input_text, 1);
166 PG_RETURN_JSONB(JsonbValueToJsonb(return_jsonb_value));
169 Datum jsonb_delete_keys(PG_FUNCTION_ARGS);
171 PG_FUNCTION_INFO_V1(jsonb_delete_keys);
174 * Operator function to delete keys from left operand where a match is found in
177 * jsonb, text[] -> jsonb
181 jsonb_delete_keys(PG_FUNCTION_ARGS)
186 /* pointers to incoming jsonb and text[] data */
187 Jsonb *input_jsonb = PG_GETARG_JSONB(0);
188 ArrayType *input_array = PG_GETARG_ARRAYTYPE_P(1);
190 /* pointers to return jsonb value data and state to be converted to jsonb on return */
191 JsonbParseState *state = NULL;
192 JsonbValue *return_jsonb_value = NULL;
194 /* pointer to iterator for input_jsonb and lookup value data */
195 JsonbIterator *jsonb_iterator;
196 JsonbValue jsonb_iterator_value;
197 int32 jsonb_iterator_token;
199 /* variables used for skip logic */
201 int32 nest_level = 0;
202 int32 array_level = 0;
204 /* array element variables for use during deconstruction */
209 /* individual array values values from incoming text[] */
210 text *array_element_text;
212 /* assert input_array is a text array type */
213 Assert(ARR_ELEMTYPE(input_array) == TEXTOID);
215 /* check input_array is one-dimensional */
216 if (ARR_NDIM(input_array) > 1)
218 (errcode(ERRCODE_ARRAY_SUBSCRIPT_ERROR),
219 errmsg("1 dimensional text array expected")));
221 /* deconstruct array elements */
222 deconstruct_array(input_array, TEXTOID, -1, false, 'i',
223 &datums, &nulls, &count);
225 /* if the array is empty there's no work to do so return the input value */
227 PG_RETURN_JSONB(input_jsonb);
230 * If we've been supplied with existing keys iterate round json data and rebuild
231 * with keys/elements excluded.
233 * skip_key, nest_level and array_level are crude counts to check if the the value
234 * for the key is closed and ensure we don't match on keys within nested objects.
235 * Because we are recursing into nested elements but blindly just pushing them onto
236 * the return value we can get away without deeper knowledge of the json?
238 jsonb_iterator = JsonbIteratorInit(&input_jsonb->root);
240 while ((jsonb_iterator_token = JsonbIteratorNext(&jsonb_iterator, &jsonb_iterator_value, false)) != WJB_DONE) {
242 switch (jsonb_iterator_token)
244 case WJB_BEGIN_ARRAY:
247 return_jsonb_value = pushJsonbValue(&state, WJB_BEGIN_ARRAY, &jsonb_iterator_value);
249 case WJB_BEGIN_OBJECT:
252 return_jsonb_value = pushJsonbValue(&state, WJB_BEGIN_OBJECT, &jsonb_iterator_value);
255 /* only match array elements if they are text or null */
256 if (skip_key == 0 && nest_level == 0 && array_level > 0)
258 if (jsonb_iterator_value.type == jbvString || jsonb_iterator_value.type == jbvNull)
260 for (i=0; i<count; i++)
263 array_element_text = DatumGetTextP(datums[i]);
265 array_element_text = NULL;
267 if (((array_element_text != NULL) && (jsonb_iterator_value.val.string.len == VARSIZE_ANY_EXHDR(array_element_text)) &&
268 (memcmp(jsonb_iterator_value.val.string.val,
269 VARDATA_ANY(array_element_text),
270 jsonb_iterator_value.val.string.len) == 0)) || ((array_element_text == NULL) && (jsonb_iterator_value.type == jbvNull)))
284 return_jsonb_value = pushJsonbValue(&state, WJB_ELEM, &jsonb_iterator_value);
287 /* Check each key against our array of keys */
292 else if (nest_level == 1 && array_level == 0)
294 for (i=0; i<count; i++)
299 array_element_text = DatumGetTextP(datums[i]);
301 if ((jsonb_iterator_value.val.string.len == VARSIZE_ANY_EXHDR(array_element_text)) &&
302 (memcmp(jsonb_iterator_value.val.string.val,
303 VARDATA_ANY(array_element_text),
304 jsonb_iterator_value.val.string.len) == 0))
313 return_jsonb_value = pushJsonbValue(&state, WJB_KEY, &jsonb_iterator_value);
317 return_jsonb_value = pushJsonbValue(&state, WJB_VALUE, &jsonb_iterator_value);
318 else if (skip_key > 0)
324 return_jsonb_value = pushJsonbValue(&state, WJB_END_ARRAY, NULL);
325 else if (skip_key > 0 && array_level == 0)
331 return_jsonb_value = pushJsonbValue(&state, WJB_END_OBJECT, NULL);
332 else if (skip_key > 0)
336 elog(ERROR, "invalid JsonbIteratorNext rc: %d", jsonb_iterator_token);
339 PG_FREE_IF_COPY(input_jsonb, 0);
340 PG_FREE_IF_COPY(input_array, 1);
342 PG_RETURN_JSONB(JsonbValueToJsonb(return_jsonb_value));
345 Datum jsonb_delete_jsonb(PG_FUNCTION_ARGS);
347 PG_FUNCTION_INFO_V1(jsonb_delete_jsonb);
350 * Operator function to delete keys and values from left operand where a match
351 * is found in the right operand.
353 * jsonb, jsonb -> jsonb
357 jsonb_delete_jsonb(PG_FUNCTION_ARGS)
359 /* pointers to incoming jsonb and text[] data */
360 Jsonb *input_jsonb_a = PG_GETARG_JSONB(0);
361 Jsonb *input_jsonb_b = PG_GETARG_JSONB(1);
363 /* pointers to return jsonb value data and state to be converted to jsonb on return */
364 JsonbValue *return_jsonb_value = NULL;
365 JsonbParseState *state = NULL;
367 /* pointer to iterator for input_jsonb_a and temporary value data */
368 JsonbIterator *jsonb_iterator;
369 JsonbValue jsonb_iterator_value;
370 JsonbValue jsonb_iterator_key;
371 int32 jsonb_iterator_token;
372 bool skip_nested = false;
376 /* pointer to lookup on input_jsonb_b */
377 JsonbValue *jsonb_lookup_value = NULL;
380 * check if either right jsonb is empty and return left if so
382 if (JB_ROOT_COUNT(input_jsonb_b) == 0)
383 PG_RETURN_JSONB(input_jsonb_a);
385 jsonb_iterator = JsonbIteratorInit(&input_jsonb_a->root);
387 while ((jsonb_iterator_token = JsonbIteratorNext(&jsonb_iterator, &jsonb_iterator_value, skip_nested)) != WJB_DONE)
392 switch (jsonb_iterator_token)
394 case WJB_BEGIN_ARRAY:
395 case WJB_BEGIN_OBJECT:
398 return_jsonb_value = pushJsonbValue(&state, jsonb_iterator_token, &jsonb_iterator_value);
402 * findJsonbValueFromContainer only supports jsonb arrays containting scalar values?
403 * If container is something like '[[1]]' or '[{"a":1}]' will error with "invalid jsonb scalar type"
405 jsonb_lookup_value = findJsonbValueFromContainer(&input_jsonb_b->root, JB_FOBJECT | JB_FARRAY, &jsonb_iterator_value);
406 if (jsonb_lookup_value == NULL)
408 if (jsonb_iterator_value.type == jbvBinary)
410 return_jsonb_value = pushJsonbBinary(state, jsonb_iterator_value.val.binary.data);
414 return_jsonb_value = pushJsonbValue(&state, WJB_ELEM, &jsonb_iterator_value);
419 jsonb_lookup_value = findJsonbValueFromContainer(&input_jsonb_b->root, JB_FOBJECT | JB_FARRAY, &jsonb_iterator_value);
421 jsonb_iterator_key = jsonb_iterator_value;
422 jsonb_iterator_token = JsonbIteratorNext(&jsonb_iterator, &jsonb_iterator_value, skip_nested);
423 if (jsonb_iterator_token != WJB_VALUE)
424 elog(ERROR, "invalid JsonbIteratorNext (expected WJB_VALUE) rc: %d", jsonb_iterator_token);
426 if (jsonb_lookup_value != NULL)
429 if (jsonb_lookup_value->type == jsonb_iterator_value.type)
431 switch (jsonb_lookup_value->type)
437 if (DatumGetBool(DirectFunctionCall2(numeric_eq,
438 PointerGetDatum(jsonb_lookup_value->val.numeric),
439 PointerGetDatum(jsonb_iterator_value.val.numeric))))
443 if ((jsonb_lookup_value->val.string.len == jsonb_iterator_value.val.string.len) &&
444 (memcmp(jsonb_lookup_value->val.string.val,
445 jsonb_iterator_value.val.string.val,
446 jsonb_lookup_value->val.string.len) == 0))
450 if ((jsonb_lookup_value->val.binary.len == jsonb_iterator_value.val.binary.len) &&
451 (memcmp(jsonb_lookup_value->val.binary.data,
452 jsonb_iterator_value.val.binary.data,
453 jsonb_lookup_value->val.binary.len) == 0))
457 if (jsonb_lookup_value->val.boolean == jsonb_iterator_value.val.boolean)
461 /* should not be possible? */
463 /* should not be possible? */
465 ereport(ERROR, (errcode(ERRCODE_SUCCESSFUL_COMPLETION), errmsg("unexpected lookup type %i", jsonb_iterator_token)));
472 return_jsonb_value = pushJsonbValue(&state, WJB_KEY, &jsonb_iterator_key);
474 /* if our value is nested binary data, iterate separately pushing each val */
475 if (jsonb_iterator_value.type == jbvBinary)
477 return_jsonb_value = pushJsonbBinary(state, jsonb_iterator_value.val.binary.data);
481 return_jsonb_value = pushJsonbValue(&state, jsonb_iterator_token, &jsonb_iterator_value);
486 /* should not be possible */
488 elog(ERROR, "invalid JsonbIteratorNext rc: %d", jsonb_iterator_token);
493 PG_FREE_IF_COPY(input_jsonb_a, 0);
494 PG_FREE_IF_COPY(input_jsonb_b, 1);
496 PG_RETURN_JSONB(JsonbValueToJsonb(return_jsonb_value));
499 Datum jsonb_delete_path(PG_FUNCTION_ARGS);
501 PG_FUNCTION_INFO_V1(jsonb_delete_path);
505 * jsonb, text[] -> jsonb
509 jsonb_delete_path(PG_FUNCTION_ARGS)
511 /* pointers to incoming jsonb and text[] data */
512 Jsonb *input_jsonb_a = PG_GETARG_JSONB(0);
513 ArrayType *input_array = PG_GETARG_ARRAYTYPE_P(1);
514 //Jsonb *input_jsonb_b = PG_GETARG_JSONB(2);
516 /* pointer to return jsonb data */
517 Jsonb *return_jsonb = NULL;
519 return_jsonb = jsonbModifyPath(input_jsonb_a, input_array, NULL);
521 PG_FREE_IF_COPY(input_jsonb_a, 0);
522 PG_FREE_IF_COPY(input_array, 1);
523 //PG_FREE_IF_COPY(input_jsonb_b, 2);
525 PG_RETURN_JSONB(return_jsonb);
528 Datum jsonb_concat_jsonb(PG_FUNCTION_ARGS);
530 PG_FUNCTION_INFO_V1(jsonb_concat_jsonb);
533 * Operator function to concatenate json from left operand where a match
534 * is found in the right operand.
536 * jsonb, jsonb -> jsonb
540 jsonb_concat_jsonb(PG_FUNCTION_ARGS)
542 /* incoming jsonb data */
543 Jsonb *input_jsonb_a = PG_GETARG_JSONB(0);
544 Jsonb *input_jsonb_b = PG_GETARG_JSONB(1);
546 /* return jsonb value data to be converted to jsonb on return */
547 JsonbParseState *state = NULL;
548 JsonbValue *return_jsonb_value = NULL;
550 /* iterator for input_jsonb_b */
551 JsonbIterator *jsonb_iterator;
552 JsonbValue jsonb_iterator_value;
553 int32 jsonb_iterator_token;
554 int32 jsonb_root_open;
555 int32 jsonb_root_close;
557 int32 nest_level = 0;
561 * check if either supplied jsonb is empty and return the other if so
563 if (JB_ROOT_COUNT(input_jsonb_a) == 0)
564 PG_RETURN_JSONB(input_jsonb_b);
565 else if (JB_ROOT_COUNT(input_jsonb_b) == 0)
566 PG_RETURN_JSONB(input_jsonb_a);
569 * rather than restrict concatenation to objects, allow any jsonb root
570 * but if one is an array use an array as the root container else
573 if (JB_ROOT_IS_ARRAY(input_jsonb_a) || JB_ROOT_IS_ARRAY(input_jsonb_b))
575 jsonb_root_open = WJB_BEGIN_ARRAY;
576 jsonb_root_close = WJB_END_ARRAY;
579 jsonb_root_open = WJB_BEGIN_OBJECT;
580 jsonb_root_close = WJB_END_OBJECT;
584 * The following is essentially a cut 'n shut job; discarding the closing root
585 * object token from the first jsonb value and the opening one from the second.
586 * Values from each are just blindly pushed onto the return value leaving
587 * deduplication down to lower level jsonb logic.
590 return_jsonb_value = pushJsonbValue(&state, jsonb_root_open, NULL);
592 jsonb_iterator = JsonbIteratorInit(&input_jsonb_a->root);
594 while ((jsonb_iterator_token = JsonbIteratorNext(&jsonb_iterator, &jsonb_iterator_value, false)) != WJB_DONE)
596 if (jsonb_iterator_token == jsonb_root_open && first)
602 else if (jsonb_iterator_token == jsonb_root_close && nest_level != 0)
610 return_jsonb_value = pushJsonbValue(&state, jsonb_iterator_token, &jsonb_iterator_value);
615 jsonb_iterator = JsonbIteratorInit(&input_jsonb_b->root);
617 while ((jsonb_iterator_token = JsonbIteratorNext(&jsonb_iterator, &jsonb_iterator_value, false)) != WJB_DONE)
619 if (jsonb_iterator_token == jsonb_root_open && first)
625 else if (jsonb_iterator_token == jsonb_root_close && nest_level != 0)
633 return_jsonb_value = pushJsonbValue(&state, jsonb_iterator_token, &jsonb_iterator_value);
636 return_jsonb_value = pushJsonbValue(&state, jsonb_root_close, NULL);
638 PG_FREE_IF_COPY(input_jsonb_a, 0);
639 PG_FREE_IF_COPY(input_jsonb_b, 1);
641 PG_RETURN_JSONB(JsonbValueToJsonb(return_jsonb_value));
644 Datum jsonb_replace_jsonb(PG_FUNCTION_ARGS);
646 PG_FUNCTION_INFO_V1(jsonb_replace_jsonb);
649 * Operator function to replace json in left operand where keys match
650 * in the right operand.
652 * jsonb, jsonb -> jsonb
656 jsonb_replace_jsonb(PG_FUNCTION_ARGS)
658 /* incoming jsonb data */
659 Jsonb *input_jsonb_a = PG_GETARG_JSONB(0);
660 Jsonb *input_jsonb_b = PG_GETARG_JSONB(1);
662 /* return jsonb value data to be converted to jsonb on return */
663 JsonbParseState *state = NULL;
664 JsonbValue *return_jsonb_value = NULL;
666 /* lookup jsonb value data */
667 JsonbValue jsonb_lookup_key;
668 JsonbValue *jsonb_lookup_value = NULL;
669 uint32 jsonb_lookup_flags;
671 /* iterator for input_jsonb_b */
672 JsonbIterator *jsonb_iterator;
673 JsonbValue jsonb_iterator_value;
674 int32 jsonb_iterator_token;
677 * check if supplied replacement jsonb is empty and return unchanged if so
679 if (JB_ROOT_COUNT(input_jsonb_b) == 0)
680 PG_RETURN_JSONB(input_jsonb_a);
682 if (JB_ROOT_IS_OBJECT(input_jsonb_a))
683 jsonb_lookup_flags = JB_FOBJECT;
685 jsonb_lookup_flags = JB_FOBJECT | JB_FARRAY;
687 jsonb_iterator = JsonbIteratorInit(&input_jsonb_a->root);
688 while ((jsonb_iterator_token = JsonbIteratorNext(&jsonb_iterator, &jsonb_iterator_value, true)) != WJB_DONE)
690 if ((jsonb_iterator_token == WJB_ELEM ) && (jsonb_iterator_value.type == jbvBinary))
692 return_jsonb_value = pushJsonbBinary(state, jsonb_iterator_value.val.binary.data);
696 return_jsonb_value = pushJsonbValue(&state, jsonb_iterator_token, &jsonb_iterator_value);
698 Assert(jsonb_iterator_token != WJB_VALUE);
700 if ( jsonb_iterator_token == WJB_KEY )
702 jsonb_lookup_key.type = jbvString;
703 jsonb_lookup_key.val.string.val = jsonb_iterator_value.val.string.val;
704 jsonb_lookup_key.val.string.len = jsonb_iterator_value.val.string.len;
706 jsonb_iterator_token = JsonbIteratorNext(&jsonb_iterator, &jsonb_iterator_value, true);
707 Assert(sonb_iterator_token == WJB_VALUE);
709 jsonb_lookup_value = findJsonbValueFromContainer(&input_jsonb_b->root,
710 jsonb_lookup_flags, &jsonb_lookup_key);
712 /* if there's nothing to replace push the original value */
713 if (jsonb_lookup_value == NULL)
715 jsonb_lookup_value = &jsonb_iterator_value;
718 /* if our value is nested binary data, iterate separately pushing each val */
719 if (jsonb_lookup_value->type == jbvBinary)
721 return_jsonb_value = pushJsonbBinary(state, jsonb_lookup_value->val.binary.data);
725 return_jsonb_value = pushJsonbValue(&state, WJB_VALUE, jsonb_lookup_value);
730 PG_FREE_IF_COPY(input_jsonb_a, 0);
731 PG_FREE_IF_COPY(input_jsonb_b, 1);
733 PG_RETURN_JSONB(JsonbValueToJsonb(return_jsonb_value));
736 Datum jsonb_replace_path(PG_FUNCTION_ARGS);
738 PG_FUNCTION_INFO_V1(jsonb_replace_path);
742 * jsonb, text[], jsonb -> jsonb
746 jsonb_replace_path(PG_FUNCTION_ARGS)
748 /* pointers to incoming jsonb and text[] data */
749 Jsonb *input_jsonb_a = PG_GETARG_JSONB(0);
750 ArrayType *input_array = PG_GETARG_ARRAYTYPE_P(1);
751 Jsonb *input_jsonb_b = PG_GETARG_JSONB(2);
753 /* pointer to return jsonb data */
754 Jsonb *return_jsonb = NULL;
756 return_jsonb = jsonbModifyPath(input_jsonb_a, input_array, input_jsonb_b);
758 PG_FREE_IF_COPY(input_jsonb_a, 0);
759 PG_FREE_IF_COPY(input_array, 1);
760 PG_FREE_IF_COPY(input_jsonb_b, 2);
762 PG_RETURN_JSONB(return_jsonb);