diff options
Diffstat (limited to 'src/json-extension-functions.cc')
| -rw-r--r-- | src/json-extension-functions.cc | 915 |
1 files changed, 915 insertions, 0 deletions
diff --git a/src/json-extension-functions.cc b/src/json-extension-functions.cc new file mode 100644 index 0000000..36dc290 --- /dev/null +++ b/src/json-extension-functions.cc @@ -0,0 +1,915 @@ +/** + * Copyright (c) 2014, Timothy Stack + * + * All rights reserved. + * + * Redistribution and use in source and binary forms, with or without + * modification, are permitted provided that the following conditions are met: + * + * * Redistributions of source code must retain the above copyright notice, this + * list of conditions and the following disclaimer. + * * Redistributions in binary form must reproduce the above copyright notice, + * this list of conditions and the following disclaimer in the documentation + * and/or other materials provided with the distribution. + * * Neither the name of Timothy Stack nor the names of its contributors + * may be used to endorse or promote products derived from this software + * without specific prior written permission. + * + * THIS SOFTWARE IS PROVIDED BY THE REGENTS AND CONTRIBUTORS ''AS IS'' AND ANY + * EXPRESS OR IMPLIED WARRANTIES, INCLUDING, BUT NOT LIMITED TO, THE IMPLIED + * WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE ARE + * DISCLAIMED. IN NO EVENT SHALL THE REGENTS OR CONTRIBUTORS BE LIABLE FOR ANY + * DIRECT, INDIRECT, INCIDENTAL, SPECIAL, EXEMPLARY, OR CONSEQUENTIAL DAMAGES + * (INCLUDING, BUT NOT LIMITED TO, PROCUREMENT OF SUBSTITUTE GOODS OR SERVICES; + * LOSS OF USE, DATA, OR PROFITS; OR BUSINESS INTERRUPTION) HOWEVER CAUSED AND + * ON ANY THEORY OF LIABILITY, WHETHER IN CONTRACT, STRICT LIABILITY, OR TORT + * (INCLUDING NEGLIGENCE OR OTHERWISE) ARISING IN ANY WAY OUT OF THE USE OF THIS + * SOFTWARE, EVEN IF ADVISED OF THE POSSIBILITY OF SUCH DAMAGE. + * + * @file json-extension-functions.cc + */ + +#include <cstring> +#include <string> + +#include "config.h" +#include "mapbox/variant.hpp" +#include "scn/scn.h" +#include "sqlite-extension-func.hh" +#include "sqlite3.h" +#include "vtab_module.hh" +#include "vtab_module_json.hh" +#include "yajl/api/yajl_gen.h" +#include "yajlpp/json_op.hh" +#include "yajlpp/yajlpp.hh" + +using namespace mapbox; + +#define JSON_SUBTYPE 74 /* Ascii for "J" */ + +class sql_json_op : public json_op { +public: + explicit sql_json_op(json_ptr& ptr) : json_op(ptr){}; + + int sjo_type{-1}; + std::string sjo_str; + int64_t sjo_int{0}; + double sjo_float{0.0}; +}; + +static void +null_or_default(sqlite3_context* context, int argc, sqlite3_value* argv[]) +{ + if (argc > 2) { + sqlite3_result_value(context, argv[2]); + } else { + sqlite3_result_null(context); + } +} + +struct contains_userdata { + util::variant<string_fragment, sqlite3_int64, bool> cu_match_value{false}; + size_t cu_depth{0}; + bool cu_result{false}; +}; + +static int +contains_string(void* ctx, const unsigned char* str, size_t len) +{ + auto sf = string_fragment::from_bytes(str, len); + auto& cu = *((contains_userdata*) ctx); + + if (cu.cu_depth <= 1 && cu.cu_match_value.get<string_fragment>() == sf) { + cu.cu_result = true; + } + + return 1; +} + +static int +contains_integer(void* ctx, long long value) +{ + auto& cu = *((contains_userdata*) ctx); + + if (cu.cu_depth <= 1 && cu.cu_match_value.get<sqlite3_int64>() == value) { + cu.cu_result = true; + } + + return 1; +} + +static int +contains_null(void* ctx) +{ + auto& cu = *((contains_userdata*) ctx); + + cu.cu_result = true; + + return 1; +} + +static bool +json_contains(vtab_types::nullable<const char> nullable_json_in, + sqlite3_value* value) +{ + if (nullable_json_in.n_value == nullptr + || nullable_json_in.n_value[0] == '\0') + { + return false; + } + + const auto* json_in = nullable_json_in.n_value; + auto_mem<yajl_handle_t> handle(yajl_free); + yajl_callbacks cb; + contains_userdata cu; + + memset(&cb, 0, sizeof(cb)); + handle = yajl_alloc(&cb, nullptr, &cu); + + cb.yajl_start_array = +[](void* ctx) { + auto& cu = *((contains_userdata*) ctx); + + cu.cu_depth += 1; + + return 1; + }; + cb.yajl_end_array = +[](void* ctx) { + auto& cu = *((contains_userdata*) ctx); + + cu.cu_depth -= 1; + + return 1; + }; + cb.yajl_start_map = +[](void* ctx) { + auto& cu = *((contains_userdata*) ctx); + + cu.cu_depth += 2; + + return 1; + }; + cb.yajl_end_map = +[](void* ctx) { + auto& cu = *((contains_userdata*) ctx); + + cu.cu_depth -= 2; + + return 1; + }; + + switch (sqlite3_value_type(value)) { + case SQLITE3_TEXT: + cb.yajl_string = contains_string; + cu.cu_match_value = string_fragment::from_bytes( + sqlite3_value_text(value), sqlite3_value_bytes(value)); + break; + case SQLITE_INTEGER: + cb.yajl_integer = contains_integer; + cu.cu_match_value = sqlite3_value_int64(value); + break; + case SQLITE_NULL: + cb.yajl_null = contains_null; + break; + } + + if (yajl_parse(handle.in(), (const unsigned char*) json_in, strlen(json_in)) + != yajl_status_ok + || yajl_complete_parse(handle.in()) != yajl_status_ok) + { + throw yajlpp_error(handle.in(), json_in, strlen(json_in)); + } + + return cu.cu_result; +} + +static int +gen_handle_null(void* ctx) +{ + sql_json_op* sjo = (sql_json_op*) ctx; + yajl_gen gen = (yajl_gen) sjo->jo_ptr_data; + + if (sjo->jo_ptr.jp_state == json_ptr::match_state_t::DONE) { + sjo->sjo_type = SQLITE_NULL; + } else { + sjo->jo_ptr_error_code = yajl_gen_null(gen); + } + + return sjo->jo_ptr_error_code == yajl_gen_status_ok; +} + +static int +gen_handle_boolean(void* ctx, int boolVal) +{ + sql_json_op* sjo = (sql_json_op*) ctx; + yajl_gen gen = (yajl_gen) sjo->jo_ptr_data; + + if (sjo->jo_ptr.jp_state == json_ptr::match_state_t::DONE) { + sjo->sjo_type = SQLITE_INTEGER; + sjo->sjo_int = boolVal; + } else { + sjo->jo_ptr_error_code = yajl_gen_bool(gen, boolVal); + } + + return sjo->jo_ptr_error_code == yajl_gen_status_ok; +} + +static int +gen_handle_string(void* ctx, const unsigned char* stringVal, size_t len) +{ + sql_json_op* sjo = (sql_json_op*) ctx; + yajl_gen gen = (yajl_gen) sjo->jo_ptr_data; + + if (sjo->jo_ptr.jp_state == json_ptr::match_state_t::DONE) { + sjo->sjo_type = SQLITE3_TEXT; + sjo->sjo_str = std::string((char*) stringVal, len); + } else { + sjo->jo_ptr_error_code = yajl_gen_string(gen, stringVal, len); + } + + return sjo->jo_ptr_error_code == yajl_gen_status_ok; +} + +static int +gen_handle_number(void* ctx, const char* numval, size_t numlen) +{ + sql_json_op* sjo = (sql_json_op*) ctx; + yajl_gen gen = (yajl_gen) sjo->jo_ptr_data; + + if (sjo->jo_ptr.jp_state == json_ptr::match_state_t::DONE) { + auto num_sv = scn::string_view{numval, numlen}; + auto scan_int_res = scn::scan_value<int64_t>(num_sv); + + if (scan_int_res && scan_int_res.empty()) { + sjo->sjo_int = scan_int_res.value(); + sjo->sjo_type = SQLITE_INTEGER; + } else { + auto scan_float_res = scn::scan_value<double>(num_sv); + + sjo->sjo_float = scan_float_res.value(); + sjo->sjo_type = SQLITE_FLOAT; + } + } else { + sjo->jo_ptr_error_code = yajl_gen_number(gen, numval, numlen); + } + + return sjo->jo_ptr_error_code == yajl_gen_status_ok; +} + +static void +sql_jget(sqlite3_context* context, int argc, sqlite3_value** argv) +{ + if (argc < 2) { + sqlite3_result_error(context, "expecting JSON value and pointer", -1); + return; + } + + if (sqlite3_value_type(argv[0]) == SQLITE_NULL) { + null_or_default(context, argc, argv); + return; + } + + const char* json_in = (const char*) sqlite3_value_text(argv[0]); + + if (sqlite3_value_type(argv[1]) == SQLITE_NULL) { + sqlite3_result_text(context, json_in, -1, SQLITE_TRANSIENT); + return; + } + + const char* ptr_in = (const char*) sqlite3_value_text(argv[1]); + json_ptr jp(ptr_in); + sql_json_op jo(jp); + auto_mem<yajl_handle_t> handle(yajl_free); + unsigned char* err; + yajlpp_gen gen; + + yajl_gen_config(gen, yajl_gen_beautify, false); + + jo.jo_ptr_callbacks = json_op::gen_callbacks; + jo.jo_ptr_callbacks.yajl_null = gen_handle_null; + jo.jo_ptr_callbacks.yajl_boolean = gen_handle_boolean; + jo.jo_ptr_callbacks.yajl_string = gen_handle_string; + jo.jo_ptr_callbacks.yajl_number = gen_handle_number; + jo.jo_ptr_data = gen.get_handle(); + + handle.reset(yajl_alloc(&json_op::ptr_callbacks, nullptr, &jo)); + switch (yajl_parse( + handle.in(), (const unsigned char*) json_in, strlen(json_in))) + { + case yajl_status_error: { + err = yajl_get_error(handle.in(), + 1, + (const unsigned char*) json_in, + strlen(json_in)); + sqlite3_result_error(context, (const char*) err, -1); + yajl_free_error(handle.in(), err); + return; + } + case yajl_status_client_canceled: + if (jo.jo_ptr.jp_state + == json_ptr::match_state_t::ERR_INVALID_ESCAPE) + { + sqlite3_result_error( + context, jo.jo_ptr.error_msg().c_str(), -1); + } else { + null_or_default(context, argc, argv); + } + return; + default: + break; + } + + switch (yajl_complete_parse(handle.in())) { + case yajl_status_error: { + err = yajl_get_error(handle.in(), + 1, + (const unsigned char*) json_in, + strlen(json_in)); + sqlite3_result_error(context, (const char*) err, -1); + yajl_free_error(handle.in(), err); + return; + } + case yajl_status_client_canceled: + if (jo.jo_ptr.jp_state + == json_ptr::match_state_t::ERR_INVALID_ESCAPE) + { + sqlite3_result_error( + context, jo.jo_ptr.error_msg().c_str(), -1); + } else { + null_or_default(context, argc, argv); + } + return; + default: + break; + } + + switch (jo.sjo_type) { + case SQLITE3_TEXT: + sqlite3_result_text(context, + jo.sjo_str.c_str(), + jo.sjo_str.size(), + SQLITE_TRANSIENT); + return; + case SQLITE_NULL: + sqlite3_result_null(context); + return; + case SQLITE_INTEGER: + sqlite3_result_int(context, jo.sjo_int); + return; + case SQLITE_FLOAT: + sqlite3_result_double(context, jo.sjo_float); + return; + } + + string_fragment result = gen.to_string_fragment(); + + if (result.empty()) { + null_or_default(context, argc, argv); + return; + } + + sqlite3_result_text( + context, result.data(), result.length(), SQLITE_TRANSIENT); +#ifdef HAVE_SQLITE3_VALUE_SUBTYPE + sqlite3_result_subtype(context, JSON_SUBTYPE); +#endif +} + +struct concat_context { + concat_context(yajl_gen gen_handle) : cc_gen_handle(gen_handle) {} + + yajl_gen cc_gen_handle; + int cc_depth{0}; +}; + +static int +concat_gen_null(void* ctx) +{ + auto* cc = static_cast<concat_context*>(ctx); + + if (cc->cc_depth > 0) { + return yajl_gen_null(cc->cc_gen_handle) == yajl_gen_status_ok; + } + + return 1; +} + +static int +concat_gen_boolean(void* ctx, int val) +{ + auto* cc = static_cast<concat_context*>(ctx); + + return yajl_gen_bool(cc->cc_gen_handle, val) == yajl_gen_status_ok; +} + +static int +concat_gen_number(void* ctx, const char* val, size_t len) +{ + auto* cc = static_cast<concat_context*>(ctx); + + return yajl_gen_number(cc->cc_gen_handle, val, len) == yajl_gen_status_ok; +} + +static int +concat_gen_string(void* ctx, const unsigned char* val, size_t len) +{ + auto* cc = static_cast<concat_context*>(ctx); + + return yajl_gen_string(cc->cc_gen_handle, val, len) == yajl_gen_status_ok; +} + +static int +concat_gen_start_map(void* ctx) +{ + auto* cc = static_cast<concat_context*>(ctx); + + cc->cc_depth += 1; + return yajl_gen_map_open(cc->cc_gen_handle) == yajl_gen_status_ok; +} + +static int +concat_gen_end_map(void* ctx) +{ + auto* cc = static_cast<concat_context*>(ctx); + + cc->cc_depth -= 1; + return yajl_gen_map_close(cc->cc_gen_handle) == yajl_gen_status_ok; +} + +static int +concat_gen_map_key(void* ctx, const unsigned char* key, size_t len) +{ + auto* cc = static_cast<concat_context*>(ctx); + + return yajl_gen_string(cc->cc_gen_handle, key, len) == yajl_gen_status_ok; +} + +static int +concat_gen_start_array(void* ctx) +{ + auto* cc = static_cast<concat_context*>(ctx); + + cc->cc_depth += 1; + if (cc->cc_depth == 1) { + return 1; + } + return yajl_gen_array_open(cc->cc_gen_handle) == yajl_gen_status_ok; +} + +static int +concat_gen_end_array(void* ctx) +{ + auto* cc = static_cast<concat_context*>(ctx); + + cc->cc_depth -= 1; + if (cc->cc_depth == 0) { + return 1; + } + return yajl_gen_array_close(cc->cc_gen_handle) == yajl_gen_status_ok; +} + +static void +concat_gen_elements(yajl_gen gen, const unsigned char* text, size_t len) +{ + auto_mem<yajl_handle_t> handle(yajl_free); + yajl_callbacks callbacks = {nullptr}; + concat_context cc{gen}; + + callbacks.yajl_null = concat_gen_null; + callbacks.yajl_boolean = concat_gen_boolean; + callbacks.yajl_number = concat_gen_number; + callbacks.yajl_string = concat_gen_string; + callbacks.yajl_start_map = concat_gen_start_map; + callbacks.yajl_end_map = concat_gen_end_map; + callbacks.yajl_map_key = concat_gen_map_key; + callbacks.yajl_start_array = concat_gen_start_array; + callbacks.yajl_end_array = concat_gen_end_array; + + handle = yajl_alloc(&callbacks, nullptr, &cc); + yajl_config(handle, yajl_allow_comments, 1); + if (yajl_parse(handle, (const unsigned char*) text, len) != yajl_status_ok + || yajl_complete_parse(handle) != yajl_status_ok) + { + std::unique_ptr<unsigned char, decltype(&free)> err_msg( + yajl_get_error(handle, 1, (const unsigned char*) text, len), free); + + throw sqlite_func_error("Invalid JSON: {}", + (const char*) err_msg.get()); + } +} + +static json_string +json_concat(nonstd::optional<const char*> json_in, + const std::vector<sqlite3_value*>& values) +{ + yajlpp_gen gen; + + yajl_gen_config(gen, yajl_gen_beautify, false); + + { + yajlpp_array array(gen); + + if (json_in) { + concat_gen_elements(gen, + (const unsigned char*) json_in.value(), + strlen(json_in.value())); + } + + for (auto* const val : values) { + switch (sqlite3_value_type(val)) { + case SQLITE_NULL: + array.gen(); + break; + case SQLITE_INTEGER: + array.gen(sqlite3_value_int64(val)); + break; + case SQLITE_FLOAT: + array.gen(sqlite3_value_double(val)); + break; + case SQLITE3_TEXT: { + const auto* text_val = sqlite3_value_text(val); + + if (sqlite3_value_subtype(val) == JSON_SUBTYPE) { + concat_gen_elements( + gen, text_val, strlen((const char*) text_val)); + } else { + array.gen((const char*) text_val); + } + break; + } + } + } + } + + return json_string(gen); +} + +#if 0 +static flattened_json_string +sql_flatten_json_object(string_fragment sf) +{ + yajlpp_gen gen; + + { + json_ptr jp("/"); + json_op jo(jp); + auto_mem<yajl_handle_t> handle(yajl_free); + + jo.jo_ptr_data = gen.get_handle(); + yajl_gen_config(gen, yajl_gen_beautify, false); + handle.reset(yajl_alloc(&json_op::gen_callbacks, nullptr, &jo)); + switch (yajl_parse( + handle.in(), (const unsigned char*) sf.data(), sf.length())) { + case yajl_status_error: + case yajl_status_client_canceled: + throw yajlpp_error(handle.in(), sf.data(), sf.length()); + case yajl_status_ok: + break; + } + switch (yajl_complete_parse(handle.in())) { + case yajl_status_error: + case yajl_status_client_canceled: + throw yajlpp_error(handle.in(), sf.data(), sf.length()); + case yajl_status_ok: + break; + } + } + + auto result = gen.to_string_fragment(); + if (!result.startswith("{") || !result.endswith("}")) { + throw std::runtime_error( + "flatten_json_object() requires a JSON object"); + } + + return flattened_json_string(gen); +} +#endif + +struct json_agg_context { + yajl_gen_t* jac_yajl_gen; +}; + +static void +sql_json_group_object_step(sqlite3_context* context, + int argc, + sqlite3_value** argv) +{ + if ((argc % 2) == 1) { + sqlite3_result_error( + context, + "Uneven number of arguments to json_group_object(), " + "expecting key and value pairs", + -1); + return; + } + + json_agg_context* jac = (json_agg_context*) sqlite3_aggregate_context( + context, sizeof(json_agg_context)); + + if (jac->jac_yajl_gen == nullptr) { + jac->jac_yajl_gen = yajl_gen_alloc(nullptr); + yajl_gen_config(jac->jac_yajl_gen, yajl_gen_beautify, false); + + yajl_gen_map_open(jac->jac_yajl_gen); + } + + for (int lpc = 0; (lpc + 1) < argc; lpc += 2) { + if (sqlite3_value_type(argv[lpc]) == SQLITE_NULL) { + continue; + } + + const unsigned char* key = sqlite3_value_text(argv[lpc]); + + yajl_gen_string(jac->jac_yajl_gen, key, strlen((const char*) key)); + + switch (sqlite3_value_type(argv[lpc + 1])) { + case SQLITE_NULL: + yajl_gen_null(jac->jac_yajl_gen); + break; + case SQLITE3_TEXT: { + const unsigned char* value = sqlite3_value_text(argv[lpc + 1]); +#ifdef HAVE_SQLITE3_VALUE_SUBTYPE + int subtype = sqlite3_value_subtype(argv[lpc + 1]); + + if (subtype == JSON_SUBTYPE) { + yajl_gen_number(jac->jac_yajl_gen, + (const char*) value, + strlen((const char*) value)); + } else { +#endif + yajl_gen_string( + jac->jac_yajl_gen, value, strlen((const char*) value)); +#ifdef HAVE_SQLITE3_VALUE_SUBTYPE + } +#endif + break; + } + case SQLITE_INTEGER: { + const unsigned char* value = sqlite3_value_text(argv[lpc + 1]); + + yajl_gen_number(jac->jac_yajl_gen, + (const char*) value, + strlen((const char*) value)); + break; + } + case SQLITE_FLOAT: { + double value = sqlite3_value_double(argv[lpc + 1]); + + yajl_gen_double(jac->jac_yajl_gen, value); + break; + } + } + } +} + +static void +sql_json_group_object_final(sqlite3_context* context) +{ + json_agg_context* jac + = (json_agg_context*) sqlite3_aggregate_context(context, 0); + + if (jac == nullptr) { + sqlite3_result_text(context, "{}", -1, SQLITE_STATIC); + } else { + const unsigned char* buf; + size_t len; + + yajl_gen_map_close(jac->jac_yajl_gen); + yajl_gen_get_buf(jac->jac_yajl_gen, &buf, &len); + sqlite3_result_text(context, (const char*) buf, len, SQLITE_TRANSIENT); +#ifdef HAVE_SQLITE3_VALUE_SUBTYPE + sqlite3_result_subtype(context, JSON_SUBTYPE); +#endif + yajl_gen_free(jac->jac_yajl_gen); + } +} + +static void +sql_json_group_array_step(sqlite3_context* context, + int argc, + sqlite3_value** argv) +{ + json_agg_context* jac = (json_agg_context*) sqlite3_aggregate_context( + context, sizeof(json_agg_context)); + + if (jac->jac_yajl_gen == nullptr) { + jac->jac_yajl_gen = yajl_gen_alloc(nullptr); + yajl_gen_config(jac->jac_yajl_gen, yajl_gen_beautify, false); + + yajl_gen_array_open(jac->jac_yajl_gen); + } + + for (int lpc = 0; lpc < argc; lpc++) { + switch (sqlite3_value_type(argv[lpc])) { + case SQLITE_NULL: + yajl_gen_null(jac->jac_yajl_gen); + break; + case SQLITE3_TEXT: { + const unsigned char* value = sqlite3_value_text(argv[lpc]); +#ifdef HAVE_SQLITE3_VALUE_SUBTYPE + int subtype = sqlite3_value_subtype(argv[lpc]); + + if (subtype == JSON_SUBTYPE) { + yajl_gen_number(jac->jac_yajl_gen, + (const char*) value, + strlen((const char*) value)); + } else { +#endif + yajl_gen_string( + jac->jac_yajl_gen, value, strlen((const char*) value)); +#ifdef HAVE_SQLITE3_VALUE_SUBTYPE + } +#endif + break; + } + case SQLITE_INTEGER: { + const unsigned char* value = sqlite3_value_text(argv[lpc]); + + yajl_gen_number(jac->jac_yajl_gen, + (const char*) value, + strlen((const char*) value)); + break; + } + case SQLITE_FLOAT: { + double value = sqlite3_value_double(argv[lpc]); + + yajl_gen_double(jac->jac_yajl_gen, value); + break; + } + } + } +} + +static void +sql_json_group_array_final(sqlite3_context* context) +{ + json_agg_context* jac + = (json_agg_context*) sqlite3_aggregate_context(context, 0); + + if (jac == nullptr) { + sqlite3_result_text(context, "[]", -1, SQLITE_STATIC); + } else { + const unsigned char* buf; + size_t len; + + yajl_gen_array_close(jac->jac_yajl_gen); + yajl_gen_get_buf(jac->jac_yajl_gen, &buf, &len); + sqlite3_result_text(context, (const char*) buf, len, SQLITE_TRANSIENT); +#ifdef HAVE_SQLITE3_VALUE_SUBTYPE + sqlite3_result_subtype(context, JSON_SUBTYPE); +#endif + yajl_gen_free(jac->jac_yajl_gen); + } +} + +int +json_extension_functions(struct FuncDef** basic_funcs, + struct FuncDefAgg** agg_funcs) +{ + static struct FuncDef json_funcs[] = { + sqlite_func_adapter<decltype(&json_concat), json_concat>::builder( + help_text("json_concat", + "Returns an array with the given values concatenated " + "onto the end. " + "If the initial value is null, the result will be an " + "array with " + "the given elements. If the initial value is an array, " + "the result " + "will be an array with the given values at the end. If " + "the initial " + "value is not null or an array, the result will be an " + "array with " + "two elements: the initial value and the given value.") + .sql_function() + .with_parameter({"json", "The initial JSON value."}) + .with_parameter( + help_text("value", + "The value(s) to add to the end of the array.") + .one_or_more()) + .with_tags({"json"}) + .with_example({ + "To append the number 4 to null", + "SELECT json_concat(NULL, 4)", + }) + .with_example({ + "To append 4 and 5 to the array [1, 2, 3]", + "SELECT json_concat('[1, 2, 3]', 4, 5)", + }) + .with_example({ + "To concatenate two arrays together", + "SELECT json_concat('[1, 2, 3]', json('[4, 5]'))", + })), + + sqlite_func_adapter<decltype(&json_contains), json_contains>::builder( + help_text("json_contains", + "Check if a JSON value contains the given element.") + .sql_function() + .with_parameter({"json", "The JSON value to query."}) + .with_parameter( + {"value", "The value to look for in the first argument"}) + .with_tags({"json"}) + .with_example({ + "To test if a JSON array contains the number 4", + "SELECT json_contains('[1, 2, 3]', 4)", + }) + .with_example({ + "To test if a JSON array contains the string 'def'", + "SELECT json_contains('[\"abc\", \"def\"]', 'def')", + })), + + { + "jget", + -1, + SQLITE_UTF8, + 0, + sql_jget, + help_text("jget", + "Get the value from a JSON object using a JSON-Pointer.") + .sql_function() + .with_parameter({"json", "The JSON object to query."}) + .with_parameter( + {"ptr", "The JSON-Pointer to lookup in the object."}) + .with_parameter( + help_text("default", + "The default value if the value was not found") + .optional()) + .with_tags({"json"}) + .with_example( + {"To get the root of a JSON value", "SELECT jget('1', '')"}) + .with_example({ + "To get the property named 'b' in a JSON object", + "SELECT jget('{ \"a\": 1, \"b\": 2 }', '/b')", + }) + .with_example({ + "To get the 'msg' property and return a default if " + "it does not exist", + "SELECT jget(null, '/msg', 'Hello')", + }), + }, + +#if 0 + sqlite_func_adapter<decltype(&sql_flatten_json_object), + sql_flatten_json_object>:: + builder(help_text("flatten_json_object", "hello") + .sql_function() + .with_parameter({"json", "The JSON"})), +#endif + + {nullptr}, + }; + + static struct FuncDefAgg json_agg_funcs[] = { + { + "json_group_object", + -1, + 0, + sql_json_group_object_step, + sql_json_group_object_final, + help_text("json_group_object") + .sql_function() + .with_summary( + "Collect the given values from a query into a JSON object") + .with_parameter( + help_text("name", "The property name for the value")) + .with_parameter( + help_text("value", "The value to add to the object") + .one_or_more()) + .with_tags({"json"}) + .with_example({"To create an object from arguments", + "SELECT json_group_object('a', 1, 'b', 2)"}) + .with_example({ + "To create an object from a pair of columns", + "SELECT json_group_object(column1, column2) FROM " + "(VALUES ('a', 1), ('b', 2))", + }), + }, + { + "json_group_array", + -1, + 0, + sql_json_group_array_step, + sql_json_group_array_final, + help_text("json_group_array") + .sql_function() + .with_summary( + "Collect the given values from a query into a JSON array") + .with_parameter( + help_text("value", "The values to append to the array") + .one_or_more()) + .with_tags({"json"}) + .with_example({ + "To create an array from arguments", + "SELECT json_group_array('one', 2, 3.4)", + }) + .with_example({ + "To create an array from a column of values", + "SELECT json_group_array(column1) FROM (VALUES " + "(1), (2), (3))", + }), + }, + + {nullptr}, + }; + + *basic_funcs = json_funcs; + *agg_funcs = json_agg_funcs; + + return SQLITE_OK; +} |