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"
21 #ifdef PG_MODULE_MAGIC
25 Datum jsonb_delete_key(PG_FUNCTION_ARGS);
27 PG_FUNCTION_INFO_V1(jsonb_delete_key);
30 * Operator function to delete key from left operand where a match is found in
33 * jsonb, text -> jsonb
37 jsonb_delete_key(PG_FUNCTION_ARGS)
39 /* pointers to incoming jsonb and text data */
40 Jsonb *input_jsonb = PG_GETARG_JSONB(0);
41 text *input_text = PG_GETARG_TEXT_P(1);
43 /* pointers to return jsonb value data and state to be converted to jsonb on return */
44 JsonbParseState *state = NULL;
45 JsonbValue *return_jsonb_value = NULL;
47 /* pointer to iterator for input_jsonb and lookup value data */
48 JsonbValue jsonb_lookup_value;
49 JsonbValue *jsonb_value = NULL;
50 JsonbIterator *jsonb_iterator;
51 JsonbValue jsonb_iterator_value;
52 int32 jsonb_iterator_token;
54 /* variables used for skip logic */
57 int32 array_level = 0;
59 /* if we are not deaing with an array first check to make sure the key exists - this is potentially just extra unwanted work */
60 if (!JB_ROOT_IS_ARRAY(input_jsonb))
62 jsonb_lookup_value.type = jbvString;
63 jsonb_lookup_value.val.string.val = VARDATA_ANY(input_text);
64 jsonb_lookup_value.val.string.len = VARSIZE_ANY_EXHDR(input_text);
66 jsonb_value = findJsonbValueFromContainer(&input_jsonb->root,
67 JB_FOBJECT | JB_FARRAY, &jsonb_lookup_value);
69 if (jsonb_value == NULL)
70 PG_RETURN_JSONB(input_jsonb);
74 * If we've been supplied with an existing key iterate round json data and rebuild with key/value excluded.
76 * skip_key, nest_level and array_level are crude counts to check if the the value for the key is closed
77 * and ensure we don't match on keys within nested objects. Because we are recursing into nested elements
78 * but blindly just pushing them onto the return value we can get away without deeper knowledge of the json?
81 jsonb_iterator = JsonbIteratorInit(&input_jsonb->root);
83 while ((jsonb_iterator_token = JsonbIteratorNext(&jsonb_iterator, &jsonb_iterator_value, false)) != WJB_DONE)
85 switch (jsonb_iterator_token)
90 return_jsonb_value = pushJsonbValue(&state, WJB_BEGIN_ARRAY, &jsonb_iterator_value);
92 case WJB_BEGIN_OBJECT:
95 return_jsonb_value = pushJsonbValue(&state, WJB_BEGIN_OBJECT, &jsonb_iterator_value);
98 /* only match array elements if they are text */
99 if (skip_key == 0 && nest_level == 0 && array_level > 0)
101 if (jsonb_iterator_value.type == jbvString)
103 if (strncmp(pnstrdup(jsonb_iterator_value.val.string.val,jsonb_iterator_value.val.string.len),
104 VARDATA_ANY(input_text), VARSIZE_ANY_EXHDR(input_text)) == 0)
109 return_jsonb_value = pushJsonbValue(&state, WJB_ELEM, &jsonb_iterator_value);
112 /* Check each key against our array of keys */
117 else if (nest_level == 1)
119 if (strncmp(pnstrdup(jsonb_iterator_value.val.string.val,jsonb_iterator_value.val.string.len),
120 VARDATA_ANY(input_text), VARSIZE_ANY_EXHDR(input_text)) == 0) {
126 return_jsonb_value = pushJsonbValue(&state, WJB_KEY, &jsonb_iterator_value);
130 return_jsonb_value = pushJsonbValue(&state, WJB_VALUE, &jsonb_iterator_value);
131 else if (skip_key > 0)
137 return_jsonb_value = pushJsonbValue(&state, WJB_END_ARRAY, NULL);
138 else if (skip_key > 0 && array_level == 0)
144 return_jsonb_value = pushJsonbValue(&state, WJB_END_OBJECT, NULL);
145 else if (skip_key > 0)
149 elog(ERROR, "invalid JsonbIteratorNext rc: %d", jsonb_iterator_token);
152 PG_FREE_IF_COPY(input_jsonb, 0);
153 PG_FREE_IF_COPY(input_text, 1);
155 PG_RETURN_JSONB(JsonbValueToJsonb(return_jsonb_value));
158 Datum jsonb_delete_keys(PG_FUNCTION_ARGS);
160 PG_FUNCTION_INFO_V1(jsonb_delete_keys);
163 * Operator function to delete keys from left operand where a match is found in
166 * jsonb, text[] -> jsonb
170 jsonb_delete_keys(PG_FUNCTION_ARGS)
175 /* pointers to incoming jsonb and text[] data */
176 Jsonb *input_jsonb = PG_GETARG_JSONB(0);
177 ArrayType *input_array = PG_GETARG_ARRAYTYPE_P(1);
179 /* pointers to return jsonb value data and state to be converted to jsonb on return */
180 JsonbParseState *state = NULL;
181 JsonbValue *return_jsonb_value = NULL;
183 /* pointer to iterator for input_jsonb and lookup value data */
184 JsonbValue jsonb_lookup_value;
185 JsonbValue *jsonb_value = NULL;
186 JsonbIterator *jsonb_iterator;
187 JsonbValue jsonb_iterator_value;
188 int32 jsonb_iterator_token;
190 /* variables used for skip logic */
192 int32 nest_level = 0;
193 int32 array_level = 0;
195 /* array element variables for use during deconstruction */
200 /* individual array values values from incoming text[] */
201 text *array_element_text;
204 /* assert input_array is a text array type */
205 Assert(ARR_ELEMTYPE(input_array) == TEXTOID);
207 /* check input_array is one-dimensional */
208 if (ARR_NDIM(input_array) > 1)
210 (errcode(ERRCODE_ARRAY_SUBSCRIPT_ERROR),
211 errmsg("1 dimensional text array expected")));
213 /* deconstruct array elements */
214 deconstruct_array(input_array, TEXTOID, -1, false, 'i',
215 &datums, &nulls, &count);
217 /* if the array is empty there's no work to do so return the input value */
219 PG_RETURN_JSONB(input_jsonb);
221 /* if we are not deaing with an array first check to make sure the key exists - this is potentially just extra unwanted work */
222 if (!JB_ROOT_IS_ARRAY(input_jsonb))
224 for (i=0; i<count; i++)
229 array_element_text = DatumGetTextP(datums[i]);
231 jsonb_lookup_value.type = jbvString;
232 jsonb_lookup_value.val.string.val = VARDATA_ANY(array_element_text);
233 jsonb_lookup_value.val.string.len = VARSIZE_ANY_EXHDR(array_element_text);
235 jsonb_value = findJsonbValueFromContainer(&input_jsonb->root,
236 JB_FOBJECT | JB_FARRAY, &jsonb_lookup_value);
238 if (jsonb_value != NULL)
246 PG_RETURN_JSONB(input_jsonb);
250 * If we've been supplied with existing keys iterate round json data matching those keys.
252 * skip_key, nest_level and array_level are crude counts to check if the the value for the key is closed
253 * and ensure we don't match on keys within nested objects. Because we are recursing into nested elements
254 * but blindly just pushing them onto the return value we can get away without deeper knowledge of the json?
257 jsonb_iterator = JsonbIteratorInit(&input_jsonb->root);
259 while ((jsonb_iterator_token = JsonbIteratorNext(&jsonb_iterator, &jsonb_iterator_value, false)) != WJB_DONE) {
261 switch (jsonb_iterator_token)
263 case WJB_BEGIN_ARRAY:
266 return_jsonb_value = pushJsonbValue(&state, WJB_BEGIN_ARRAY, &jsonb_iterator_value);
268 case WJB_BEGIN_OBJECT:
271 return_jsonb_value = pushJsonbValue(&state, WJB_BEGIN_OBJECT, &jsonb_iterator_value);
274 /* only match array elements if they are text */
275 if (skip_key == 0 && nest_level == 0 && array_level > 0)
277 if (jsonb_iterator_value.type == jbvString)
279 for (i=0; i<count; i++)
284 array_element_text = DatumGetTextP(datums[i]);
286 if (strncmp(pnstrdup(jsonb_iterator_value.val.string.val,jsonb_iterator_value.val.string.len),
287 VARDATA_ANY(array_element_text), VARSIZE_ANY_EXHDR(array_element_text)) == 0)
301 return_jsonb_value = pushJsonbValue(&state, WJB_ELEM, &jsonb_iterator_value);
304 /* Check each key against our array of keys */
309 else if (nest_level == 1)
311 for (i=0; i<count; i++)
316 array_element_text = DatumGetTextP(datums[i]);
318 if (strncmp(pnstrdup(jsonb_iterator_value.val.string.val,jsonb_iterator_value.val.string.len),
319 VARDATA_ANY(array_element_text), VARSIZE_ANY_EXHDR(array_element_text)) == 0)
328 return_jsonb_value = pushJsonbValue(&state, WJB_KEY, &jsonb_iterator_value);
332 return_jsonb_value = pushJsonbValue(&state, WJB_VALUE, &jsonb_iterator_value);
333 else if (skip_key > 0)
339 return_jsonb_value = pushJsonbValue(&state, WJB_END_ARRAY, NULL);
340 else if (skip_key > 0 && array_level == 0)
346 return_jsonb_value = pushJsonbValue(&state, WJB_END_OBJECT, NULL);
347 else if (skip_key > 0)
351 elog(ERROR, "invalid JsonbIteratorNext rc: %d", jsonb_iterator_token);
354 PG_FREE_IF_COPY(input_jsonb, 0);
355 PG_FREE_IF_COPY(input_array, 1);
357 PG_RETURN_JSONB(JsonbValueToJsonb(return_jsonb_value));
360 Datum jsonb_delete_jsonb(PG_FUNCTION_ARGS);
362 PG_FUNCTION_INFO_V1(jsonb_delete_jsonb);
365 * Operator function to delete keys and values from left operand where a match
366 * is found in the right operand.
368 * jsonb, jsonb -> jsonb
372 jsonb_delete_jsonb(PG_FUNCTION_ARGS)
374 /* pointers to incoming jsonb and text[] data */
375 Jsonb *input_jsonb_a = PG_GETARG_JSONB(0);
376 Jsonb *input_jsonb_b = PG_GETARG_JSONB(1);
378 /* pointers to return jsonb value data and state to be converted to jsonb on return */
379 JsonbValue *return_jsonb_value = NULL;
380 JsonbParseState *state = NULL;
382 /* pointer to iterator for input_jsonb_a and temporary value data */
383 JsonbIterator *jsonb_iterator;
384 JsonbValue jsonb_iterator_value;
385 JsonbValue jsonb_iterator_key;
386 int32 jsonb_iterator_token;
387 bool skip_nested = false;
389 /* pointer to iterator and container for pushing nested parts of input_jsonb_a */
390 JsonbContainer *nest_jsonb_container_a;
391 JsonbIterator *nest_jsonb_iterator;
394 /* pointer to lookup on input_jsonb_b */
395 JsonbValue *jsonb_lookup_value = NULL;
398 * check if either right jsonb is empty and return left if so
400 if (JB_ROOT_COUNT(input_jsonb_b) == 0)
401 PG_RETURN_JSONB(input_jsonb_a);
403 jsonb_iterator = JsonbIteratorInit(&input_jsonb_a->root);
405 while ((jsonb_iterator_token = JsonbIteratorNext(&jsonb_iterator, &jsonb_iterator_value, skip_nested)) != WJB_DONE)
410 switch (jsonb_iterator_token)
412 case WJB_BEGIN_ARRAY:
413 case WJB_BEGIN_OBJECT:
416 return_jsonb_value = pushJsonbValue(&state, jsonb_iterator_token, &jsonb_iterator_value);
419 jsonb_lookup_value = findJsonbValueFromContainer(&input_jsonb_b->root, JB_FOBJECT | JB_FARRAY, &jsonb_iterator_value);
420 if (jsonb_lookup_value == NULL)
421 return_jsonb_value = pushJsonbValue(&state, WJB_ELEM, &jsonb_iterator_value);
424 jsonb_lookup_value = findJsonbValueFromContainer(&input_jsonb_b->root, JB_FOBJECT | JB_FARRAY, &jsonb_iterator_value);
426 jsonb_iterator_key = jsonb_iterator_value;
427 jsonb_iterator_token = JsonbIteratorNext(&jsonb_iterator, &jsonb_iterator_value, skip_nested);
428 if (jsonb_iterator_token != WJB_VALUE)
429 elog(ERROR, "invalid JsonbIteratorNext (expected WJB_VALUE) rc: %d", jsonb_iterator_token);
431 if (jsonb_lookup_value != NULL)
433 if (jsonb_lookup_value->type == jsonb_iterator_value.type)
435 switch (jsonb_lookup_value->type)
439 DatumGetCString(DirectFunctionCall1(numeric_out, PointerGetDatum(jsonb_lookup_value->val.numeric))),
440 DatumGetCString(DirectFunctionCall1(numeric_out, PointerGetDatum(jsonb_iterator_value.val.numeric)))
446 pnstrdup(jsonb_lookup_value->val.string.val,jsonb_lookup_value->val.string.len),
447 pnstrdup(jsonb_iterator_value.val.string.val,jsonb_iterator_value.val.string.len)
453 JsonbToCString(NULL, jsonb_lookup_value->val.binary.data, jsonb_lookup_value->val.binary.len),
454 JsonbToCString(NULL, jsonb_iterator_value.val.binary.data, jsonb_lookup_value->val.binary.len)
459 if (jsonb_lookup_value->val.boolean == jsonb_iterator_value.val.boolean)
463 /* should not be possible? */
465 /* should not be possible? */
467 ereport(ERROR, (errcode(ERRCODE_SUCCESSFUL_COMPLETION), errmsg("unexpected lookup type %i", jsonb_iterator_token)));
474 return_jsonb_value = pushJsonbValue(&state, WJB_KEY, &jsonb_iterator_key);
476 /* if our value is nested binary data, iterate separately pushing each val */
477 if (jsonb_iterator_value.type == jbvBinary)
479 nest_jsonb_container_a = jsonb_iterator_value.val.binary.data;
481 nest_jsonb_iterator = JsonbIteratorInit(nest_jsonb_container_a);
482 while ((jsonb_iterator_token = JsonbIteratorNext(&nest_jsonb_iterator, &jsonb_iterator_value, false)) != WJB_DONE)
484 return_jsonb_value = pushJsonbValue(&state, jsonb_iterator_token, &jsonb_iterator_value);
489 return_jsonb_value = pushJsonbValue(&state, jsonb_iterator_token, &jsonb_iterator_value);
494 /* should not be possible */
496 elog(ERROR, "invalid JsonbIteratorNext rc: %d", jsonb_iterator_token);
501 PG_FREE_IF_COPY(input_jsonb_a, 0);
502 PG_FREE_IF_COPY(input_jsonb_b, 1);
504 PG_RETURN_JSONB(JsonbValueToJsonb(return_jsonb_value));
507 Datum jsonb_concat_jsonb(PG_FUNCTION_ARGS);
509 PG_FUNCTION_INFO_V1(jsonb_concat_jsonb);
512 * Operator function to concatenate json from left operand where a match
513 * is found in the right operand.
515 * jsonb, jsonb -> jsonb
519 jsonb_concat_jsonb(PG_FUNCTION_ARGS)
521 /* incoming jsonb and text[] data */
522 Jsonb *input_jsonb_a = PG_GETARG_JSONB(0);
523 Jsonb *input_jsonb_b = PG_GETARG_JSONB(1);
525 /* return jsonb value data to be converted to jsonb on return */
526 JsonbParseState *state = NULL;
527 JsonbValue *return_jsonb_value = NULL;
529 /* iterator for input_jsonb_b */
530 JsonbIterator *jsonb_iterator;
531 JsonbValue jsonb_iterator_value;
532 int32 jsonb_iterator_token;
533 int32 jsonb_root_open;
534 int32 jsonb_root_close;
536 int32 nest_level = 0;
539 * check if either supplied jsonb is empty and return the other if so
541 if (JB_ROOT_COUNT(input_jsonb_a) == 0)
542 PG_RETURN_JSONB(input_jsonb_b);
543 else if (JB_ROOT_COUNT(input_jsonb_b) == 0)
544 PG_RETURN_JSONB(input_jsonb_a);
547 * rather than restrict concatenation to objects, allow any jsonb root
548 * but if one is an array use an array as the root container else
551 if (JB_ROOT_IS_ARRAY(input_jsonb_a) || JB_ROOT_IS_ARRAY(input_jsonb_b))
553 jsonb_root_open = WJB_BEGIN_ARRAY;
554 jsonb_root_close = WJB_END_ARRAY;
557 jsonb_root_open = WJB_BEGIN_OBJECT;
558 jsonb_root_close = WJB_END_OBJECT;
562 * The following is essentially a cut 'n shut job; discarding the closing root
563 * object token from the first jsonb value and the opening one from the second.
564 * Values from each are just blindly pushed onto the return value leaving
565 * deduplication down to lower level jsonb logic.
568 return_jsonb_value = pushJsonbValue(&state, jsonb_root_open, NULL);
570 jsonb_iterator = JsonbIteratorInit(&input_jsonb_a->root);
572 while ((jsonb_iterator_token = JsonbIteratorNext(&jsonb_iterator, &jsonb_iterator_value, false)) != WJB_DONE)
574 if (jsonb_iterator_token == jsonb_root_open)
580 else if (jsonb_iterator_token == jsonb_root_close)
587 return_jsonb_value = pushJsonbValue(&state, jsonb_iterator_token, &jsonb_iterator_value);
591 jsonb_iterator = JsonbIteratorInit(&input_jsonb_b->root);
593 while ((jsonb_iterator_token = JsonbIteratorNext(&jsonb_iterator, &jsonb_iterator_value, false)) != WJB_DONE)
595 if (jsonb_iterator_token == jsonb_root_open)
601 else if (jsonb_iterator_token == jsonb_root_close)
608 return_jsonb_value = pushJsonbValue(&state, jsonb_iterator_token, &jsonb_iterator_value);
611 return_jsonb_value = pushJsonbValue(&state, jsonb_root_close, NULL);
613 PG_FREE_IF_COPY(input_jsonb_a, 0);
614 PG_FREE_IF_COPY(input_jsonb_b, 1);
616 PG_RETURN_JSONB(JsonbValueToJsonb(return_jsonb_value));