SQLite

Check-in [7f386a9332]
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
Timelines: family | ancestors | descendants | both | trunk
Files: files | file ages | folders
SHA1: 7f386a9332237100a345035ca213327e21d95855
User & Date: drh 2015-12-30 01:07:02.009
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: 0a99a8c4fa user: drh tags: trunk)
13:36
Enhance the command-line shell to handle MBCS characters on input and output. (Closed-Leaf check-in: a0a08b8c0b user: drh tags: mbcs-shell)
01:07
Add the json_group_array() and json_group_object() aggregate functions to the JSON1 extension. (check-in: 7f386a9332 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: 7c7b7f2630 user: drh tags: trunk)
Changes
Unified Diff Ignore Whitespace Patch
Changes to ext/misc/json1.c.
1177
1178
1179
1180
1181
1182
1183
1184
1185
1186
1187
1188
1189
1190
1191
){
  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.
*/







|







1177
1178
1179
1180
1181
1182
1183
1184
1185
1186
1187
1188
1189
1190
1191
){
  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.
*/
1509
1510
1511
1512
1513
1514
1515
































































































1516
1517
1518
1519
1520
1521
1522
  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 {







>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>







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
  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 {
2008
2009
2010
2011
2012
2013
2014









2015
2016
2017
2018
2019
2020
2021
2022
2023
2024
2025
2026
2027
2028
2029





2030
2031
2032
2033
2034
2035
2036

#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;
}







>
>
>
>
>
>
>
>
>















>
>
>
>
>







2104
2105
2106
2107
2108
2109
2110
2111
2112
2113
2114
2115
2116
2117
2118
2119
2120
2121
2122
2123
2124
2125
2126
2127
2128
2129
2130
2131
2132
2133
2134
2135
2136
2137
2138
2139
2140
2141
2142
2143
2144
2145
2146

#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