/ Check-in [7f386a93]
Login

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:7f386a9332237100a345035ca213327e21d95855
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
Hide Diffs Side-by-Side Diffs Ignore Whitespace Patch

Changes to ext/misc/json1.c.

  1177   1177   ){
  1178   1178     UNUSED_PARAM(argc);
  1179   1179     sqlite3_result_int(ctx, sqlite3_value_subtype(argv[0])==JSON_SUBTYPE);
  1180   1180   }
  1181   1181   #endif /* SQLITE_DEBUG */
  1182   1182   
  1183   1183   /****************************************************************************
  1184         -** SQL function implementations
         1184  +** Scalar SQL function implementations
  1185   1185   ****************************************************************************/
  1186   1186   
  1187   1187   /*
  1188   1188   ** Implementation of the json_array(VALUE,...) function.  Return a JSON
  1189   1189   ** array that contains all values given in arguments.  Or if any argument
  1190   1190   ** is a BLOB, throw an error.
  1191   1191   */
................................................................................
  1509   1509     UNUSED_PARAM(argc);
  1510   1510     if( jsonParse(&x, 0, (const char*)sqlite3_value_text(argv[0]))==0 ){
  1511   1511       rc = 1;
  1512   1512     }
  1513   1513     jsonParseReset(&x);
  1514   1514     sqlite3_result_int(ctx, rc);
  1515   1515   }
         1516  +
         1517  +
         1518  +/****************************************************************************
         1519  +** Aggregate SQL function implementations
         1520  +****************************************************************************/
         1521  +/*
         1522  +** json_group_array(VALUE)
         1523  +**
         1524  +** Return a JSON array composed of all values in the aggregate.
         1525  +*/
         1526  +static void jsonArrayStep(
         1527  +  sqlite3_context *ctx,
         1528  +  int argc,
         1529  +  sqlite3_value **argv
         1530  +){
         1531  +  JsonString *pStr;
         1532  +  pStr = (JsonString*)sqlite3_aggregate_context(ctx, sizeof(*pStr));
         1533  +  if( pStr ){
         1534  +    if( pStr->zBuf==0 ){
         1535  +      jsonInit(pStr, ctx);
         1536  +      jsonAppendChar(pStr, '[');
         1537  +    }else{
         1538  +      jsonAppendChar(pStr, ',');
         1539  +      pStr->pCtx = ctx;
         1540  +    }
         1541  +    jsonAppendValue(pStr, argv[0]);
         1542  +  }
         1543  +}
         1544  +static void jsonArrayFinal(sqlite3_context *ctx){
         1545  +  JsonString *pStr;
         1546  +  pStr = (JsonString*)sqlite3_aggregate_context(ctx, 0);
         1547  +  if( pStr ){
         1548  +    pStr->pCtx = ctx;
         1549  +    jsonAppendChar(pStr, ']');
         1550  +    if( pStr->bErr ){
         1551  +      sqlite3_result_error_nomem(ctx);
         1552  +      if( !pStr->bStatic ) sqlite3_free(pStr->zBuf);
         1553  +    }else{
         1554  +      sqlite3_result_text(ctx, pStr->zBuf, pStr->nUsed,
         1555  +                          pStr->bStatic ? SQLITE_TRANSIENT : sqlite3_free);
         1556  +      pStr->bStatic = 1;
         1557  +    }
         1558  +  }else{
         1559  +    sqlite3_result_text(ctx, "[]", 2, SQLITE_STATIC);
         1560  +  }
         1561  +  sqlite3_result_subtype(ctx, JSON_SUBTYPE);
         1562  +}
         1563  +
         1564  +/*
         1565  +** json_group_obj(NAME,VALUE)
         1566  +**
         1567  +** Return a JSON object composed of all names and values in the aggregate.
         1568  +*/
         1569  +static void jsonObjectStep(
         1570  +  sqlite3_context *ctx,
         1571  +  int argc,
         1572  +  sqlite3_value **argv
         1573  +){
         1574  +  JsonString *pStr;
         1575  +  const char *z;
         1576  +  u32 n;
         1577  +  pStr = (JsonString*)sqlite3_aggregate_context(ctx, sizeof(*pStr));
         1578  +  if( pStr ){
         1579  +    if( pStr->zBuf==0 ){
         1580  +      jsonInit(pStr, ctx);
         1581  +      jsonAppendChar(pStr, '{');
         1582  +    }else{
         1583  +      jsonAppendChar(pStr, ',');
         1584  +      pStr->pCtx = ctx;
         1585  +    }
         1586  +    z = (const char*)sqlite3_value_text(argv[0]);
         1587  +    n = (u32)sqlite3_value_bytes(argv[0]);
         1588  +    jsonAppendString(pStr, z, n);
         1589  +    jsonAppendChar(pStr, ':');
         1590  +    jsonAppendValue(pStr, argv[1]);
         1591  +  }
         1592  +}
         1593  +static void jsonObjectFinal(sqlite3_context *ctx){
         1594  +  JsonString *pStr;
         1595  +  pStr = (JsonString*)sqlite3_aggregate_context(ctx, 0);
         1596  +  if( pStr ){
         1597  +    jsonAppendChar(pStr, '}');
         1598  +    if( pStr->bErr ){
         1599  +      sqlite3_result_error_nomem(ctx);
         1600  +      if( !pStr->bStatic ) sqlite3_free(pStr->zBuf);
         1601  +    }else{
         1602  +      sqlite3_result_text(ctx, pStr->zBuf, pStr->nUsed,
         1603  +                          pStr->bStatic ? SQLITE_TRANSIENT : sqlite3_free);
         1604  +      pStr->bStatic = 1;
         1605  +    }
         1606  +  }else{
         1607  +    sqlite3_result_text(ctx, "{}", 2, SQLITE_STATIC);
         1608  +  }
         1609  +  sqlite3_result_subtype(ctx, JSON_SUBTYPE);
         1610  +}
         1611  +
  1516   1612   
  1517   1613   #ifndef SQLITE_OMIT_VIRTUALTABLE
  1518   1614   /****************************************************************************
  1519   1615   ** The json_each virtual table
  1520   1616   ****************************************************************************/
  1521   1617   typedef struct JsonEachCursor JsonEachCursor;
  1522   1618   struct JsonEachCursor {
................................................................................
  2008   2104   
  2009   2105   #if SQLITE_DEBUG
  2010   2106       /* DEBUG and TESTING functions */
  2011   2107       { "json_parse",           1, 0,   jsonParseFunc         },
  2012   2108       { "json_test1",           1, 0,   jsonTest1Func         },
  2013   2109   #endif
  2014   2110     };
         2111  +  static const struct {
         2112  +     const char *zName;
         2113  +     int nArg;
         2114  +     void (*xStep)(sqlite3_context*,int,sqlite3_value**);
         2115  +     void (*xFinal)(sqlite3_context*);
         2116  +  } aAgg[] = {
         2117  +    { "json_group_array",     1,   jsonArrayStep,   jsonArrayFinal  },
         2118  +    { "json_group_object",    2,   jsonObjectStep,  jsonObjectFinal },
         2119  +  };
  2015   2120   #ifndef SQLITE_OMIT_VIRTUALTABLE
  2016   2121     static const struct {
  2017   2122        const char *zName;
  2018   2123        sqlite3_module *pModule;
  2019   2124     } aMod[] = {
  2020   2125       { "json_each",            &jsonEachModule               },
  2021   2126       { "json_tree",            &jsonTreeModule               },
................................................................................
  2023   2128   #endif
  2024   2129     for(i=0; i<sizeof(aFunc)/sizeof(aFunc[0]) && rc==SQLITE_OK; i++){
  2025   2130       rc = sqlite3_create_function(db, aFunc[i].zName, aFunc[i].nArg,
  2026   2131                                    SQLITE_UTF8 | SQLITE_DETERMINISTIC, 
  2027   2132                                    (void*)&aFunc[i].flag,
  2028   2133                                    aFunc[i].xFunc, 0, 0);
  2029   2134     }
         2135  +  for(i=0; i<sizeof(aAgg)/sizeof(aAgg[0]) && rc==SQLITE_OK; i++){
         2136  +    rc = sqlite3_create_function(db, aAgg[i].zName, aAgg[i].nArg,
         2137  +                                 SQLITE_UTF8 | SQLITE_DETERMINISTIC, 0,
         2138  +                                 0, aAgg[i].xStep, aAgg[i].xFinal);
         2139  +  }
  2030   2140   #ifndef SQLITE_OMIT_VIRTUALTABLE
  2031   2141     for(i=0; i<sizeof(aMod)/sizeof(aMod[0]) && rc==SQLITE_OK; i++){
  2032   2142       rc = sqlite3_create_module(db, aMod[i].zName, aMod[i].pModule, 0);
  2033   2143     }
  2034   2144   #endif
  2035   2145     return rc;
  2036   2146   }

Added test/json103.test.

            1  +# 2015-12-30
            2  +#
            3  +# The author disclaims copyright to this source code.  In place of
            4  +# a legal notice, here is a blessing:
            5  +#
            6  +#    May you do good and not evil.
            7  +#    May you find forgiveness for yourself and forgive others.
            8  +#    May you share freely, never taking more than you give.
            9  +#
           10  +#***********************************************************************
           11  +# This file implements tests for JSON aggregate SQL functions
           12  +#
           13  +
           14  +set testdir [file dirname $argv0]
           15  +source $testdir/tester.tcl
           16  +
           17  +ifcapable !json1 {
           18  +  finish_test
           19  +  return
           20  +}
           21  +
           22  +do_execsql_test json103-100 {
           23  +  CREATE TABLE t1(a,b,c);
           24  +  WITH RECURSIVE c(x) AS (VALUES(1) UNION SELECT x+1 FROM c WHERE x<100)
           25  +  INSERT INTO t1(a,b,c) SELECT x, x%3, printf('n%d',x)  FROM c;
           26  +  UPDATE t1 SET a='orange' WHERE rowid=39;
           27  +  UPDATE t1 SET a=32.5 WHERE rowid=31;
           28  +  UPDATE t1 SET a=x'303132' WHERE rowid=29;
           29  +  UPDATE t1 SET a=NULL WHERE rowid=37;
           30  +  SELECT json_group_array(a) FROM t1 WHERE a<0 AND typeof(a)!='blob';
           31  +} {{[]}}
           32  +do_catchsql_test json103-101 {
           33  +  SELECT json_group_array(a) FROM t1;
           34  +} {1 {JSON cannot hold BLOB values}}
           35  +do_execsql_test json103-110 {
           36  +  SELECT json_group_array(a) FROM t1
           37  +   WHERE rowid BETWEEN 31 AND 39;
           38  +} {{[32.5,32,33,34,35,36,null,38,"orange"]}}
           39  +do_execsql_test json103-111 {
           40  +  SELECT json_array_length(json_group_array(a)) FROM t1
           41  +   WHERE rowid BETWEEN 31 AND 39;
           42  +} {9}
           43  +do_execsql_test json103-120 {
           44  +  SELECT b, json_group_array(a) FROM t1 WHERE rowid<10 GROUP BY b ORDER BY b;
           45  +} {0 {[3,6,9]} 1 {[1,4,7]} 2 {[2,5,8]}}
           46  +
           47  +do_execsql_test json103-200 {
           48  +  SELECT json_group_object(c,a) FROM t1 WHERE a<0 AND typeof(a)!='blob';
           49  +} {{{}}}
           50  +do_catchsql_test json103-201 {
           51  +  SELECT json_group_object(c,a) FROM t1;
           52  +} {1 {JSON cannot hold BLOB values}}
           53  +
           54  +do_execsql_test json103-210 {
           55  +  SELECT json_group_object(c,a) FROM t1
           56  +   WHERE rowid BETWEEN 31 AND 39 AND rowid%2==1;
           57  +} {{{"n31":32.5,"n33":33,"n35":35,"n37":null,"n39":"orange"}}}
           58  +do_execsql_test json103-220 {
           59  +  SELECT b, json_group_object(c,a) FROM t1
           60  +   WHERE rowid<7 GROUP BY b ORDER BY b;
           61  +} {0 {{"n3":3,"n6":6}} 1 {{"n1":1,"n4":4}} 2 {{"n2":2,"n5":5}}}
           62  +
           63  +
           64  +
           65  +finish_test