Many hyperlinks are disabled.
Use anonymous login
to enable hyperlinks.
Overview
| Comment: | Add the json_group_array() and json_group_object() aggregate functions to the JSON1 extension. |
|---|---|
| Downloads: | Tarball | ZIP archive | SQL archive |
| Timelines: | family | ancestors | descendants | both | trunk |
| Files: | files | file ages | folders |
| SHA1: |
7f386a9332237100a345035ca213327e |
| User & Date: | drh 2015-12-30 01:07:02 |
Context
|
2015-12-30
| ||
| 14:06 | Remove unnecessary tests from the LIKE pattern matcher. Slightly faster and slightly smaller, and it should also now works with EBCDIC. check-in: 0a99a8c4 user: drh tags: trunk | |
| 13:36 | Enhance the command-line shell to handle MBCS characters on input and output. Closed-Leaf check-in: a0a08b8c user: drh tags: mbcs-shell | |
| 01:07 | Add the json_group_array() and json_group_object() aggregate functions to the JSON1 extension. check-in: 7f386a93 user: drh tags: trunk | |
|
2015-12-24
| ||
| 14:53 | Avoid a harmless compiler warning on systems where the byteorder cannot be determined at compile-time. check-in: 7c7b7f26 user: drh tags: trunk | |
Changes
Changes to ext/misc/json1.c.
1177 1178 1179 1180 1181 1182 1183 1184 1185 1186 1187 1188 1189 1190 1191 .... 1509 1510 1511 1512 1513 1514 1515 1516 1517 1518 1519 1520 1521 1522 .... 2008 2009 2010 2011 2012 2013 2014 2015 2016 2017 2018 2019 2020 2021 .... 2023 2024 2025 2026 2027 2028 2029 2030 2031 2032 2033 2034 2035 2036 |
){
UNUSED_PARAM(argc);
sqlite3_result_int(ctx, sqlite3_value_subtype(argv[0])==JSON_SUBTYPE);
}
#endif /* SQLITE_DEBUG */
/****************************************************************************
** SQL function implementations
****************************************************************************/
/*
** Implementation of the json_array(VALUE,...) function. Return a JSON
** array that contains all values given in arguments. Or if any argument
** is a BLOB, throw an error.
*/
................................................................................
UNUSED_PARAM(argc);
if( jsonParse(&x, 0, (const char*)sqlite3_value_text(argv[0]))==0 ){
rc = 1;
}
jsonParseReset(&x);
sqlite3_result_int(ctx, rc);
}
#ifndef SQLITE_OMIT_VIRTUALTABLE
/****************************************************************************
** The json_each virtual table
****************************************************************************/
typedef struct JsonEachCursor JsonEachCursor;
struct JsonEachCursor {
................................................................................
#if SQLITE_DEBUG
/* DEBUG and TESTING functions */
{ "json_parse", 1, 0, jsonParseFunc },
{ "json_test1", 1, 0, jsonTest1Func },
#endif
};
#ifndef SQLITE_OMIT_VIRTUALTABLE
static const struct {
const char *zName;
sqlite3_module *pModule;
} aMod[] = {
{ "json_each", &jsonEachModule },
{ "json_tree", &jsonTreeModule },
................................................................................
#endif
for(i=0; i<sizeof(aFunc)/sizeof(aFunc[0]) && rc==SQLITE_OK; i++){
rc = sqlite3_create_function(db, aFunc[i].zName, aFunc[i].nArg,
SQLITE_UTF8 | SQLITE_DETERMINISTIC,
(void*)&aFunc[i].flag,
aFunc[i].xFunc, 0, 0);
}
#ifndef SQLITE_OMIT_VIRTUALTABLE
for(i=0; i<sizeof(aMod)/sizeof(aMod[0]) && rc==SQLITE_OK; i++){
rc = sqlite3_create_module(db, aMod[i].zName, aMod[i].pModule, 0);
}
#endif
return rc;
}
|
| > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > |
1177 1178 1179 1180 1181 1182 1183 1184 1185 1186 1187 1188 1189 1190 1191 .... 1509 1510 1511 1512 1513 1514 1515 1516 1517 1518 1519 1520 1521 1522 1523 1524 1525 1526 1527 1528 1529 1530 1531 1532 1533 1534 1535 1536 1537 1538 1539 1540 1541 1542 1543 1544 1545 1546 1547 1548 1549 1550 1551 1552 1553 1554 1555 1556 1557 1558 1559 1560 1561 1562 1563 1564 1565 1566 1567 1568 1569 1570 1571 1572 1573 1574 1575 1576 1577 1578 1579 1580 1581 1582 1583 1584 1585 1586 1587 1588 1589 1590 1591 1592 1593 1594 1595 1596 1597 1598 1599 1600 1601 1602 1603 1604 1605 1606 1607 1608 1609 1610 1611 1612 1613 1614 1615 1616 1617 1618 .... 2104 2105 2106 2107 2108 2109 2110 2111 2112 2113 2114 2115 2116 2117 2118 2119 2120 2121 2122 2123 2124 2125 2126 .... 2128 2129 2130 2131 2132 2133 2134 2135 2136 2137 2138 2139 2140 2141 2142 2143 2144 2145 2146 |
){
UNUSED_PARAM(argc);
sqlite3_result_int(ctx, sqlite3_value_subtype(argv[0])==JSON_SUBTYPE);
}
#endif /* SQLITE_DEBUG */
/****************************************************************************
** Scalar SQL function implementations
****************************************************************************/
/*
** Implementation of the json_array(VALUE,...) function. Return a JSON
** array that contains all values given in arguments. Or if any argument
** is a BLOB, throw an error.
*/
................................................................................
UNUSED_PARAM(argc);
if( jsonParse(&x, 0, (const char*)sqlite3_value_text(argv[0]))==0 ){
rc = 1;
}
jsonParseReset(&x);
sqlite3_result_int(ctx, rc);
}
/****************************************************************************
** Aggregate SQL function implementations
****************************************************************************/
/*
** json_group_array(VALUE)
**
** Return a JSON array composed of all values in the aggregate.
*/
static void jsonArrayStep(
sqlite3_context *ctx,
int argc,
sqlite3_value **argv
){
JsonString *pStr;
pStr = (JsonString*)sqlite3_aggregate_context(ctx, sizeof(*pStr));
if( pStr ){
if( pStr->zBuf==0 ){
jsonInit(pStr, ctx);
jsonAppendChar(pStr, '[');
}else{
jsonAppendChar(pStr, ',');
pStr->pCtx = ctx;
}
jsonAppendValue(pStr, argv[0]);
}
}
static void jsonArrayFinal(sqlite3_context *ctx){
JsonString *pStr;
pStr = (JsonString*)sqlite3_aggregate_context(ctx, 0);
if( pStr ){
pStr->pCtx = ctx;
jsonAppendChar(pStr, ']');
if( pStr->bErr ){
sqlite3_result_error_nomem(ctx);
if( !pStr->bStatic ) sqlite3_free(pStr->zBuf);
}else{
sqlite3_result_text(ctx, pStr->zBuf, pStr->nUsed,
pStr->bStatic ? SQLITE_TRANSIENT : sqlite3_free);
pStr->bStatic = 1;
}
}else{
sqlite3_result_text(ctx, "[]", 2, SQLITE_STATIC);
}
sqlite3_result_subtype(ctx, JSON_SUBTYPE);
}
/*
** json_group_obj(NAME,VALUE)
**
** Return a JSON object composed of all names and values in the aggregate.
*/
static void jsonObjectStep(
sqlite3_context *ctx,
int argc,
sqlite3_value **argv
){
JsonString *pStr;
const char *z;
u32 n;
pStr = (JsonString*)sqlite3_aggregate_context(ctx, sizeof(*pStr));
if( pStr ){
if( pStr->zBuf==0 ){
jsonInit(pStr, ctx);
jsonAppendChar(pStr, '{');
}else{
jsonAppendChar(pStr, ',');
pStr->pCtx = ctx;
}
z = (const char*)sqlite3_value_text(argv[0]);
n = (u32)sqlite3_value_bytes(argv[0]);
jsonAppendString(pStr, z, n);
jsonAppendChar(pStr, ':');
jsonAppendValue(pStr, argv[1]);
}
}
static void jsonObjectFinal(sqlite3_context *ctx){
JsonString *pStr;
pStr = (JsonString*)sqlite3_aggregate_context(ctx, 0);
if( pStr ){
jsonAppendChar(pStr, '}');
if( pStr->bErr ){
sqlite3_result_error_nomem(ctx);
if( !pStr->bStatic ) sqlite3_free(pStr->zBuf);
}else{
sqlite3_result_text(ctx, pStr->zBuf, pStr->nUsed,
pStr->bStatic ? SQLITE_TRANSIENT : sqlite3_free);
pStr->bStatic = 1;
}
}else{
sqlite3_result_text(ctx, "{}", 2, SQLITE_STATIC);
}
sqlite3_result_subtype(ctx, JSON_SUBTYPE);
}
#ifndef SQLITE_OMIT_VIRTUALTABLE
/****************************************************************************
** The json_each virtual table
****************************************************************************/
typedef struct JsonEachCursor JsonEachCursor;
struct JsonEachCursor {
................................................................................
#if SQLITE_DEBUG
/* DEBUG and TESTING functions */
{ "json_parse", 1, 0, jsonParseFunc },
{ "json_test1", 1, 0, jsonTest1Func },
#endif
};
static const struct {
const char *zName;
int nArg;
void (*xStep)(sqlite3_context*,int,sqlite3_value**);
void (*xFinal)(sqlite3_context*);
} aAgg[] = {
{ "json_group_array", 1, jsonArrayStep, jsonArrayFinal },
{ "json_group_object", 2, jsonObjectStep, jsonObjectFinal },
};
#ifndef SQLITE_OMIT_VIRTUALTABLE
static const struct {
const char *zName;
sqlite3_module *pModule;
} aMod[] = {
{ "json_each", &jsonEachModule },
{ "json_tree", &jsonTreeModule },
................................................................................
#endif
for(i=0; i<sizeof(aFunc)/sizeof(aFunc[0]) && rc==SQLITE_OK; i++){
rc = sqlite3_create_function(db, aFunc[i].zName, aFunc[i].nArg,
SQLITE_UTF8 | SQLITE_DETERMINISTIC,
(void*)&aFunc[i].flag,
aFunc[i].xFunc, 0, 0);
}
for(i=0; i<sizeof(aAgg)/sizeof(aAgg[0]) && rc==SQLITE_OK; i++){
rc = sqlite3_create_function(db, aAgg[i].zName, aAgg[i].nArg,
SQLITE_UTF8 | SQLITE_DETERMINISTIC, 0,
0, aAgg[i].xStep, aAgg[i].xFinal);
}
#ifndef SQLITE_OMIT_VIRTUALTABLE
for(i=0; i<sizeof(aMod)/sizeof(aMod[0]) && rc==SQLITE_OK; i++){
rc = sqlite3_create_module(db, aMod[i].zName, aMod[i].pModule, 0);
}
#endif
return rc;
}
|
Added test/json103.test.
> > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > |
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 |
# 2015-12-30 # # The author disclaims copyright to this source code. In place of # a legal notice, here is a blessing: # # May you do good and not evil. # May you find forgiveness for yourself and forgive others. # May you share freely, never taking more than you give. # #*********************************************************************** # This file implements tests for JSON aggregate SQL functions # set testdir [file dirname $argv0] source $testdir/tester.tcl ifcapable !json1 { finish_test return } do_execsql_test json103-100 { CREATE TABLE t1(a,b,c); WITH RECURSIVE c(x) AS (VALUES(1) UNION SELECT x+1 FROM c WHERE x<100) INSERT INTO t1(a,b,c) SELECT x, x%3, printf('n%d',x) FROM c; UPDATE t1 SET a='orange' WHERE rowid=39; UPDATE t1 SET a=32.5 WHERE rowid=31; UPDATE t1 SET a=x'303132' WHERE rowid=29; UPDATE t1 SET a=NULL WHERE rowid=37; SELECT json_group_array(a) FROM t1 WHERE a<0 AND typeof(a)!='blob'; } {{[]}} do_catchsql_test json103-101 { SELECT json_group_array(a) FROM t1; } {1 {JSON cannot hold BLOB values}} do_execsql_test json103-110 { SELECT json_group_array(a) FROM t1 WHERE rowid BETWEEN 31 AND 39; } {{[32.5,32,33,34,35,36,null,38,"orange"]}} do_execsql_test json103-111 { SELECT json_array_length(json_group_array(a)) FROM t1 WHERE rowid BETWEEN 31 AND 39; } {9} do_execsql_test json103-120 { SELECT b, json_group_array(a) FROM t1 WHERE rowid<10 GROUP BY b ORDER BY b; } {0 {[3,6,9]} 1 {[1,4,7]} 2 {[2,5,8]}} do_execsql_test json103-200 { SELECT json_group_object(c,a) FROM t1 WHERE a<0 AND typeof(a)!='blob'; } {{{}}} do_catchsql_test json103-201 { SELECT json_group_object(c,a) FROM t1; } {1 {JSON cannot hold BLOB values}} do_execsql_test json103-210 { SELECT json_group_object(c,a) FROM t1 WHERE rowid BETWEEN 31 AND 39 AND rowid%2==1; } {{{"n31":32.5,"n33":33,"n35":35,"n37":null,"n39":"orange"}}} do_execsql_test json103-220 { SELECT b, json_group_object(c,a) FROM t1 WHERE rowid<7 GROUP BY b ORDER BY b; } {0 {{"n3":3,"n6":6}} 1 {{"n1":1,"n4":4}} 2 {{"n2":2,"n5":5}}} finish_test |