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_text(PG_FUNCTION_ARGS);
27 PG_FUNCTION_INFO_V1(jsonb_delete_text);
30 * Operator function to delete keys from left operand where a match is found in
33 * jsonb, text[] -> jsonb
37 jsonb_delete_text(PG_FUNCTION_ARGS)
42 /* pointers to incoming jsonb and text[] data */
43 Jsonb *input_jsonb = PG_GETARG_JSONB(0);
44 ArrayType *input_array = PG_GETARG_ARRAYTYPE_P(1);
46 /* pointers to return jsonb_value data and state to be converted to jsonb on return */
47 JsonbParseState *state = NULL;
48 JsonbValue *return_jsonb_value = NULL;
50 /* pointer to iterator for input_jsonb and lookup value data */
51 JsonbValue jsonb_lookup_value;
52 JsonbValue *jsonb_value = NULL;
53 JsonbIterator *jsonb_iterator;
54 JsonbValue jsonb_iterator_value;
55 int32 jsonb_iterator_token;
57 /* variables used for skip logic */
60 int32 array_level = 0;
62 /* array element variables for use during deconstruction */
67 /* individual array values values from incoming text[] */
68 text *array_element_text;
71 /* check that supplied jsonb isn't non object, i.e. scalar or array */
72 if (!JB_ROOT_IS_OBJECT(input_jsonb))
74 (errcode(ERRCODE_INVALID_PARAMETER_VALUE),
75 errmsg("cannot call on a non-object")));
77 /* assert input_array is a text array type */
78 Assert(ARR_ELEMTYPE(input_array) == TEXTOID);
80 /* check input_array is one-dimensional */
81 if (ARR_NDIM(input_array) > 1)
83 (errcode(ERRCODE_ARRAY_SUBSCRIPT_ERROR),
84 errmsg("1 dimensional text array expected")));
86 /* deconstruct array elements */
87 deconstruct_array(input_array, TEXTOID, -1, false, 'i',
88 &datums, &nulls, &count);
90 /* if the array is empty there's no work to do so return the input value */
92 PG_RETURN_JSONB(input_jsonb);
94 /* first check to make sure at least one key exists - this is potentially just extra unwanted work */
95 for (i=0; i<count; i++)
100 array_element_text = DatumGetTextP(datums[i]);
102 jsonb_lookup_value.type = jbvString;
103 jsonb_lookup_value.val.string.val = VARDATA_ANY(array_element_text);
104 jsonb_lookup_value.val.string.len = VARSIZE_ANY_EXHDR(array_element_text);
106 jsonb_value = findJsonbValueFromContainer(&input_jsonb->root,
107 JB_FOBJECT | JB_FARRAY, &jsonb_lookup_value);
109 if (jsonb_value != NULL) {
116 PG_RETURN_JSONB(input_jsonb);
119 * If we've been supplied with existing keys iterate round json data matching those keys.
121 * skip_key, nest_level and array_level are crude counts to check if the the value for the key is closed
122 * and ensure we don't match on keys within nested objects. Because we are recursing into nested elements
123 * but blindly just pushing them onto the return value we can get away without deeper knowledge of the json?
126 jsonb_iterator = JsonbIteratorInit(&input_jsonb->root);
128 while ((jsonb_iterator_token = JsonbIteratorNext(&jsonb_iterator, &jsonb_iterator_value, false)) != WJB_DONE) {
130 switch (jsonb_iterator_token)
132 case WJB_BEGIN_ARRAY:
135 return_jsonb_value = pushJsonbValue(&state, WJB_BEGIN_ARRAY, &jsonb_iterator_value);
137 case WJB_BEGIN_OBJECT:
140 return_jsonb_value = pushJsonbValue(&state, WJB_BEGIN_OBJECT, &jsonb_iterator_value);
143 /* Check each key against our array of keys */
147 else if (nest_level == 1){
148 for (i=0; i<count; i++)
153 array_element_text = DatumGetTextP(datums[i]);
155 if (strcmp(VARDATA(array_element_text), pnstrdup(jsonb_iterator_value.val.string.val,jsonb_iterator_value.val.string.len)) == 0) {
163 return_jsonb_value = pushJsonbValue(&state, WJB_KEY, &jsonb_iterator_value);
167 return_jsonb_value = pushJsonbValue(&state, WJB_VALUE, &jsonb_iterator_value);
168 else if (skip_key > 0)
173 return_jsonb_value = pushJsonbValue(&state, WJB_ELEM, &jsonb_iterator_value);
178 return_jsonb_value = pushJsonbValue(&state, WJB_END_ARRAY, NULL);
179 else if (skip_key > 0 && array_level == 0)
185 return_jsonb_value = pushJsonbValue(&state, WJB_END_OBJECT, NULL);
186 else if (skip_key > 0)
190 elog(ERROR, "invalid JsonbIteratorNext rc: %d", jsonb_iterator_token);
193 PG_FREE_IF_COPY(input_jsonb, 0);
194 PG_FREE_IF_COPY(input_array, 1);
196 PG_RETURN_JSONB(JsonbValueToJsonb(return_jsonb_value));
199 Datum jsonb_delete_jsonb(PG_FUNCTION_ARGS);
201 PG_FUNCTION_INFO_V1(jsonb_delete_jsonb);
204 * Operator function to delete keys and values from left operand where a match
205 * is found in the right operand.
207 * jsonb, jsonb -> jsonb
211 jsonb_delete_jsonb(PG_FUNCTION_ARGS)
213 /* pointers to incoming jsonb and text[] data */
214 Jsonb *input_jsonb_a = PG_GETARG_JSONB(0);
215 Jsonb *input_jsonb_b = PG_GETARG_JSONB(1);
217 /* pointers to return jsonb_value data and state to be converted to jsonb on return */
218 JsonbValue *return_jsonb_value = NULL;
219 JsonbParseState *state = NULL;
221 /* pointer to iterator for input_jsonb_a and temporary value data */
222 JsonbIterator *jsonb_iterator;
223 JsonbValue jsonb_iterator_value;
224 JsonbValue jsonb_iterator_key;
225 int32 jsonb_iterator_token;
226 bool skip_nested = false;
228 /* pointer to iterator and container for pushing nested parts of input_jsonb_a */
229 JsonbContainer *nest_jsonb_container_a;
230 JsonbIterator *nest_jsonb_iterator;
233 /* inner iterator for iterating around jbvBinary types */
234 JsonbValue nest_jsonb_iterator_value;
235 int32 nest_jsonb_iterator_token;
237 /* pointer to lookup on input_jsonb_b */
238 JsonbValue *jsonb_lookup_value = NULL;
240 /* check that supplied jsonb isn't non object, i.e. scalar or array */
241 if (!JB_ROOT_IS_OBJECT(input_jsonb_a) || !JB_ROOT_IS_OBJECT(input_jsonb_b))
243 (errcode(ERRCODE_INVALID_PARAMETER_VALUE),
244 errmsg("cannot call on a non-object")));
247 * check if either supplied jsonb is empty and return the other if so
248 * this idea was copied from https://github.com/erthalion/jsonbx/blob/master/jsonbx.c
250 if (JB_ROOT_COUNT(input_jsonb_b) == 0)
251 PG_RETURN_JSONB(input_jsonb_a);
253 jsonb_iterator = JsonbIteratorInit(&input_jsonb_a->root);
255 while ((jsonb_iterator_token = JsonbIteratorNext(&jsonb_iterator, &jsonb_iterator_value, skip_nested)) != WJB_DONE) {
258 switch (jsonb_iterator_token)
260 case WJB_BEGIN_ARRAY:
261 case WJB_BEGIN_OBJECT:
265 return_jsonb_value = pushJsonbValue(&state, jsonb_iterator_token, &jsonb_iterator_value);
268 jsonb_lookup_value = findJsonbValueFromContainer(&input_jsonb_b->root, JB_FOBJECT | JB_FARRAY, &jsonb_iterator_value);
269 jsonb_iterator_key = jsonb_iterator_value;
271 jsonb_iterator_token = JsonbIteratorNext(&jsonb_iterator, &jsonb_iterator_value, skip_nested);
272 if (jsonb_iterator_token != WJB_VALUE)
273 elog(ERROR, "invalid JsonbIteratorNext (expected WJB_VALUE) rc: %d", jsonb_iterator_token);
275 if (jsonb_lookup_value != NULL) {
276 if (jsonb_lookup_value->type == jsonb_iterator_value.type) {
277 switch (jsonb_lookup_value->type) {
280 DatumGetCString(DirectFunctionCall1(numeric_out, PointerGetDatum(jsonb_lookup_value->val.numeric))),
281 DatumGetCString(DirectFunctionCall1(numeric_out, PointerGetDatum(jsonb_iterator_value.val.numeric)))
287 pnstrdup(jsonb_lookup_value->val.string.val,jsonb_lookup_value->val.string.len),
288 pnstrdup(jsonb_iterator_value.val.string.val,jsonb_iterator_value.val.string.len)
294 JsonbToCString(NULL, jsonb_lookup_value->val.binary.data, jsonb_lookup_value->val.binary.len),
295 JsonbToCString(NULL, jsonb_iterator_value.val.binary.data, jsonb_lookup_value->val.binary.len)
300 if (jsonb_lookup_value->val.boolean == jsonb_iterator_value.val.boolean)
304 /* should not be possible? */
306 /* should not be possible? */
308 ereport(NOTICE, (errcode(ERRCODE_SUCCESSFUL_COMPLETION), errmsg("unexpected lookup type")));
314 return_jsonb_value = pushJsonbValue(&state, WJB_KEY, &jsonb_iterator_key);
316 /* if our value is nested binary data, iterate separately pushing each val */
317 if (jsonb_iterator_value.type == jbvBinary) {
318 nest_jsonb_container_a = jsonb_iterator_value.val.binary.data;
320 nest_jsonb_iterator = JsonbIteratorInit(nest_jsonb_container_a);
321 while ((nest_jsonb_iterator_token = JsonbIteratorNext(&nest_jsonb_iterator, &nest_jsonb_iterator_value, false)) != WJB_DONE) {
322 return_jsonb_value = pushJsonbValue(&state, nest_jsonb_iterator_token, &nest_jsonb_iterator_value);
326 return_jsonb_value = pushJsonbValue(&state, jsonb_iterator_token, &jsonb_iterator_value);
333 /* should not be possible */
335 elog(ERROR, "invalid JsonbIteratorNext rc: %d", jsonb_iterator_token);
340 PG_FREE_IF_COPY(input_jsonb_a, 0);
341 PG_FREE_IF_COPY(input_jsonb_b, 1);
343 PG_RETURN_JSONB(JsonbValueToJsonb(return_jsonb_value));
346 Datum jsonb_concat_jsonb(PG_FUNCTION_ARGS);
348 PG_FUNCTION_INFO_V1(jsonb_concat_jsonb);
351 * Operator function to concatenate json from left operand where a match
352 * is found in the right operand.
354 * jsonb, jsonb -> jsonb
358 jsonb_concat_jsonb(PG_FUNCTION_ARGS)
360 /* incoming jsonb and text[] data */
361 Jsonb *input_jsonb_a = PG_GETARG_JSONB(0);
362 Jsonb *input_jsonb_b = PG_GETARG_JSONB(1);
364 /* return jsonb_value data to be converted to jsonb on return */
365 JsonbValue *return_jsonb_value = NULL;
367 /* iterator for input_jsonb_b */
368 JsonbIterator *jsonb_iterator;
369 JsonbValue jsonb_iterator_value;
370 int32 jsonb_iterator_token;
372 JsonbParseState *state = NULL;
373 bool skip_nested = false;
375 int32 nest_level = 0;
377 /* check that supplied jsonb isn't non object, i.e. scalar or array */
378 if (!JB_ROOT_IS_OBJECT(input_jsonb_a) || !JB_ROOT_IS_OBJECT(input_jsonb_b))
380 (errcode(ERRCODE_INVALID_PARAMETER_VALUE),
381 errmsg("cannot call on a non-object")));
384 * check if either supplied jsonb is empty and return the other if so
385 * this idea was copied from https://github.com/erthalion/jsonbx/blob/master/jsonbx.c
387 if (JB_ROOT_COUNT(input_jsonb_a) == 0)
388 PG_RETURN_JSONB(input_jsonb_b);
389 else if (JB_ROOT_COUNT(input_jsonb_b) == 0)
390 PG_RETURN_JSONB(input_jsonb_a);
393 * The following is essentially a cut 'n shut job; discarding the closing root
394 * object token from the first jsonb value and the opening one from the second.
395 * Values from each are just blindly pushed onto the return value leaving
396 * deduplication down to lower level jsonb logic.
399 jsonb_iterator = JsonbIteratorInit(&input_jsonb_a->root);
401 while ((jsonb_iterator_token = JsonbIteratorNext(&jsonb_iterator, &jsonb_iterator_value, skip_nested)) != WJB_DONE) {
403 switch (jsonb_iterator_token)
405 case WJB_BEGIN_ARRAY:
410 return_jsonb_value = pushJsonbValue(&state, jsonb_iterator_token, &jsonb_iterator_value);
412 case WJB_BEGIN_OBJECT:
413 return_jsonb_value = pushJsonbValue(&state, jsonb_iterator_token, &jsonb_iterator_value);
419 return_jsonb_value = pushJsonbValue(&state, jsonb_iterator_token, &jsonb_iterator_value);
422 elog(ERROR, "invalid JsonbIteratorNext rc: %d", jsonb_iterator_token);
428 jsonb_iterator = JsonbIteratorInit(&input_jsonb_b->root);
430 while ((jsonb_iterator_token = JsonbIteratorNext(&jsonb_iterator, &jsonb_iterator_value, skip_nested)) != WJB_DONE) {
432 switch (jsonb_iterator_token)
434 case WJB_BEGIN_ARRAY:
439 return_jsonb_value = pushJsonbValue(&state, jsonb_iterator_token, &jsonb_iterator_value);
441 case WJB_BEGIN_OBJECT:
443 return_jsonb_value = pushJsonbValue(&state, jsonb_iterator_token, &jsonb_iterator_value);
448 return_jsonb_value = pushJsonbValue(&state, jsonb_iterator_token, &jsonb_iterator_value);
451 elog(ERROR, "invalid JsonbIteratorNext rc: %d", jsonb_iterator_token);
456 PG_FREE_IF_COPY(input_jsonb_a, 0);
457 PG_FREE_IF_COPY(input_jsonb_b, 1);
459 PG_RETURN_JSONB(JsonbValueToJsonb(return_jsonb_value));