/ Check-in [916cdc83]
Login

Many hyperlinks are disabled.
Use anonymous login to enable hyperlinks.

Overview
Comment:Get the json_group_array() and json_group_object() SQL functions working as window functions.
Downloads: Tarball | ZIP archive | SQL archive
Timelines: family | ancestors | descendants | both | trunk
Files: files | file ages | folders
SHA3-256:916cdc83f5a45e0b6f61c52ff5fde70d54bcd0dfaa4a32f9ac709fe0ddbb480b
User & Date: drh 2018-07-05 20:05:29
Context
2018-07-05
20:33
Update the recipe for resetting a database using SQLITE_DBCONFIG_RESET_DATABASE. check-in: c43dd23f user: dan tags: trunk
20:05
Get the json_group_array() and json_group_object() SQL functions working as window functions. check-in: 916cdc83 user: drh tags: trunk
18:34
Return an error if a "RANGE" window-frame uses "<expr> PRECEDING" or "<expr> FOLLOWING". check-in: 786c87ba user: dan tags: trunk
Changes
Hide Diffs Side-by-Side Diffs Ignore Whitespace Patch

Changes to ext/misc/json1.c.

  1798   1798       }else{
  1799   1799         jsonAppendChar(pStr, ',');
  1800   1800         pStr->pCtx = ctx;
  1801   1801       }
  1802   1802       jsonAppendValue(pStr, argv[0]);
  1803   1803     }
  1804   1804   }
  1805         -static void jsonArrayFinal(sqlite3_context *ctx){
         1805  +static void jsonArrayCompute(sqlite3_context *ctx, int isFinal){
  1806   1806     JsonString *pStr;
  1807   1807     pStr = (JsonString*)sqlite3_aggregate_context(ctx, 0);
  1808   1808     if( pStr ){
  1809   1809       pStr->pCtx = ctx;
  1810   1810       jsonAppendChar(pStr, ']');
  1811   1811       if( pStr->bErr ){
  1812   1812         if( pStr->bErr==1 ) sqlite3_result_error_nomem(ctx);
  1813   1813         assert( pStr->bStatic );
  1814         -    }else{
         1814  +    }else if( isFinal ){
  1815   1815         sqlite3_result_text(ctx, pStr->zBuf, pStr->nUsed,
  1816   1816                             pStr->bStatic ? SQLITE_TRANSIENT : sqlite3_free);
  1817   1817         pStr->bStatic = 1;
         1818  +    }else{
         1819  +      sqlite3_result_text(ctx, pStr->zBuf, pStr->nUsed, SQLITE_TRANSIENT);
         1820  +      pStr->nUsed--;
  1818   1821       }
  1819   1822     }else{
  1820   1823       sqlite3_result_text(ctx, "[]", 2, SQLITE_STATIC);
  1821   1824     }
  1822   1825     sqlite3_result_subtype(ctx, JSON_SUBTYPE);
  1823   1826   }
         1827  +static void jsonArrayValue(sqlite3_context *ctx){
         1828  +  jsonArrayCompute(ctx, 0);
         1829  +}
         1830  +static void jsonArrayFinal(sqlite3_context *ctx){
         1831  +  jsonArrayCompute(ctx, 1);
         1832  +}
         1833  +
         1834  +#ifndef SQLITE_OMIT_WINDOWFUNC
         1835  +/*
         1836  +** This method works for both json_group_array() and json_group_object().
         1837  +** It works by removing the first element of the group by searching forward
         1838  +** to the first comma (",") that is not within a string and deleting all
         1839  +** text through that comma.
         1840  +*/
         1841  +static void jsonGroupInverse(
         1842  +  sqlite3_context *ctx,
         1843  +  int argc,
         1844  +  sqlite3_value **argv
         1845  +){
         1846  +  int i;
         1847  +  int inStr = 0;
         1848  +  char *z;
         1849  +  JsonString *pStr;
         1850  +  pStr = (JsonString*)sqlite3_aggregate_context(ctx, 0);
         1851  +  if( !pStr ) return;
         1852  +  z = pStr->zBuf;
         1853  +  for(i=1; z[i]!=',' || inStr; i++){
         1854  +    assert( i<pStr->nUsed );
         1855  +    if( z[i]=='"' ){
         1856  +      inStr = !inStr;
         1857  +    }else if( z[i]=='\\' ){
         1858  +      i++;
         1859  +    }
         1860  +  }
         1861  +  pStr->nUsed -= i;      
         1862  +  memmove(&z[1], &z[i+1], pStr->nUsed-1);
         1863  +}
         1864  +#else
         1865  +# define jsonGroupInverse 0
         1866  +#endif
         1867  +
  1824   1868   
  1825   1869   /*
  1826   1870   ** json_group_obj(NAME,VALUE)
  1827   1871   **
  1828   1872   ** Return a JSON object composed of all names and values in the aggregate.
  1829   1873   */
  1830   1874   static void jsonObjectStep(
................................................................................
  1848   1892       z = (const char*)sqlite3_value_text(argv[0]);
  1849   1893       n = (u32)sqlite3_value_bytes(argv[0]);
  1850   1894       jsonAppendString(pStr, z, n);
  1851   1895       jsonAppendChar(pStr, ':');
  1852   1896       jsonAppendValue(pStr, argv[1]);
  1853   1897     }
  1854   1898   }
  1855         -static void jsonObjectFinal(sqlite3_context *ctx){
         1899  +static void jsonObjectCompute(sqlite3_context *ctx, int isFinal){
  1856   1900     JsonString *pStr;
  1857   1901     pStr = (JsonString*)sqlite3_aggregate_context(ctx, 0);
  1858   1902     if( pStr ){
  1859   1903       jsonAppendChar(pStr, '}');
  1860   1904       if( pStr->bErr ){
  1861   1905         if( pStr->bErr==1 ) sqlite3_result_error_nomem(ctx);
  1862   1906         assert( pStr->bStatic );
  1863         -    }else{
         1907  +    }else if( isFinal ){
  1864   1908         sqlite3_result_text(ctx, pStr->zBuf, pStr->nUsed,
  1865   1909                             pStr->bStatic ? SQLITE_TRANSIENT : sqlite3_free);
  1866   1910         pStr->bStatic = 1;
         1911  +    }else{
         1912  +      sqlite3_result_text(ctx, pStr->zBuf, pStr->nUsed, SQLITE_TRANSIENT);
         1913  +      pStr->nUsed--;
  1867   1914       }
  1868   1915     }else{
  1869   1916       sqlite3_result_text(ctx, "{}", 2, SQLITE_STATIC);
  1870   1917     }
  1871   1918     sqlite3_result_subtype(ctx, JSON_SUBTYPE);
  1872   1919   }
         1920  +static void jsonObjectValue(sqlite3_context *ctx){
         1921  +  jsonObjectCompute(ctx, 0);
         1922  +}
         1923  +static void jsonObjectFinal(sqlite3_context *ctx){
         1924  +  jsonObjectCompute(ctx, 1);
         1925  +}
         1926  +
  1873   1927   
  1874   1928   
  1875   1929   #ifndef SQLITE_OMIT_VIRTUALTABLE
  1876   1930   /****************************************************************************
  1877   1931   ** The json_each virtual table
  1878   1932   ****************************************************************************/
  1879   1933   typedef struct JsonEachCursor JsonEachCursor;
................................................................................
  2373   2427   #endif
  2374   2428     };
  2375   2429     static const struct {
  2376   2430        const char *zName;
  2377   2431        int nArg;
  2378   2432        void (*xStep)(sqlite3_context*,int,sqlite3_value**);
  2379   2433        void (*xFinal)(sqlite3_context*);
         2434  +     void (*xValue)(sqlite3_context*);
  2380   2435     } aAgg[] = {
  2381         -    { "json_group_array",     1,   jsonArrayStep,   jsonArrayFinal  },
  2382         -    { "json_group_object",    2,   jsonObjectStep,  jsonObjectFinal },
         2436  +    { "json_group_array",     1,
         2437  +      jsonArrayStep,   jsonArrayFinal,  jsonArrayValue  },
         2438  +    { "json_group_object",    2,
         2439  +      jsonObjectStep,  jsonObjectFinal, jsonObjectValue },
  2383   2440     };
  2384   2441   #ifndef SQLITE_OMIT_VIRTUALTABLE
  2385   2442     static const struct {
  2386   2443        const char *zName;
  2387   2444        sqlite3_module *pModule;
  2388   2445     } aMod[] = {
  2389   2446       { "json_each",            &jsonEachModule               },
................................................................................
  2393   2450     for(i=0; i<sizeof(aFunc)/sizeof(aFunc[0]) && rc==SQLITE_OK; i++){
  2394   2451       rc = sqlite3_create_function(db, aFunc[i].zName, aFunc[i].nArg,
  2395   2452                                    SQLITE_UTF8 | SQLITE_DETERMINISTIC, 
  2396   2453                                    (void*)&aFunc[i].flag,
  2397   2454                                    aFunc[i].xFunc, 0, 0);
  2398   2455     }
  2399   2456     for(i=0; i<sizeof(aAgg)/sizeof(aAgg[0]) && rc==SQLITE_OK; i++){
  2400         -    rc = sqlite3_create_function(db, aAgg[i].zName, aAgg[i].nArg,
         2457  +    rc = sqlite3_create_window_function(db, aAgg[i].zName, aAgg[i].nArg,
  2401   2458                                    SQLITE_UTF8 | SQLITE_DETERMINISTIC, 0,
  2402         -                                 0, aAgg[i].xStep, aAgg[i].xFinal);
         2459  +                                 aAgg[i].xStep, aAgg[i].xFinal,
         2460  +                                 aAgg[i].xValue, jsonGroupInverse, 0);
  2403   2461     }
  2404   2462   #ifndef SQLITE_OMIT_VIRTUALTABLE
  2405   2463     for(i=0; i<sizeof(aMod)/sizeof(aMod[0]) && rc==SQLITE_OK; i++){
  2406   2464       rc = sqlite3_create_module(db, aMod[i].zName, aMod[i].pModule, 0);
  2407   2465     }
  2408   2466   #endif
  2409   2467     return rc;

Changes to src/vdbe.c.

  6407   6407   ** for an aggregate and store the result in P1. Or, if P3 is non-zero,
  6408   6408   ** invoke the xValue() function and store the result in register P3.
  6409   6409   **
  6410   6410   ** P2 is the number of arguments that the step function takes and
  6411   6411   ** P4 is a pointer to the FuncDef for this function.  The P2
  6412   6412   ** argument is not used by this opcode.  It is only there to disambiguate
  6413   6413   ** functions that can take varying numbers of arguments.  The
  6414         -** P4 argument is only needed for the degenerate case where
         6414  +** P4 argument is only needed for the case where
  6415   6415   ** the step function was not previously called.
  6416   6416   */
  6417   6417   case OP_AggFinal: {
  6418   6418     Mem *pMem;
  6419   6419     assert( pOp->p1>0 && pOp->p1<=(p->nMem+1 - p->nCursor) );
  6420   6420     pMem = &aMem[pOp->p1];
  6421   6421     assert( (pMem->flags & ~(MEM_Null|MEM_Agg))==0 );

Changes to test/json103.test.

    70     70     CREATE TABLE t1(x);
    71     71     INSERT INTO t1 VALUES(1),('abc');
    72     72     SELECT
    73     73        json_group_array(x),
    74     74        json_group_array(json_object('x',x))
    75     75       FROM t1;
    76     76   } {{[1,"abc"]} {[{"x":1},{"x":"abc"}]}}
           77  +
           78  +# json_group_array() and json_group_object() work as window functions.
           79  +#
           80  +ifcapable windowfunc {
           81  +  do_execsql_test json103-400 {
           82  +    CREATE TABLE t4(x);
           83  +    INSERT INTO t4 VALUES
           84  +      (1),
           85  +      ('a,b'),
           86  +      (3),
           87  +      ('x"y'),
           88  +      (5),
           89  +      (6),
           90  +      (7);
           91  +    SELECT json_group_array(x) OVER (ROWS 2 PRECEDING) FROM t4;
           92  +  } {{[1]} {[1,"a,b"]} {[1,"a,b",3]} {["a,b",3,"x\"y"]} {[3,"x\"y",5]} {["x\"y",5,6]} {[5,6,7]}}
           93  +  do_execsql_test json103-410 {
           94  +    SELECT json_group_object(rowid, x) OVER (ROWS 2 PRECEDING) FROM t4;
           95  +  } {{{"1":1}} {{"1":1,"2":"a,b"}} {{"1":1,"2":"a,b","3":3}} {{"2":"a,b","3":3,"4":"x\"y"}} {{"3":3,"4":"x\"y","5":5}} {{"4":"x\"y","5":5,"6":6}} {{"5":5,"6":6,"7":7}}}
           96  +}
    77     97   
    78     98   finish_test