/ Check-in [08c36e45]
Login

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

Overview
Comment:Add the json_tree() virtual table.
Downloads: Tarball | ZIP archive | SQL archive
Timelines: family | ancestors | descendants | both | json
Files: files | file ages | folders
SHA1: 08c36e45f0d3a7b89caf823652d7543b76ac802a
User & Date: drh 2015-08-21 17:33:11
Context
2015-08-21
19:56
Merge header file fixes from trunk. check-in: 7c2713e9 user: drh tags: json
17:33
Add the json_tree() virtual table. check-in: 08c36e45 user: drh tags: json
17:16
Merge in trunk fixes for table-valued functions. check-in: 67375f32 user: drh tags: json
Changes
Hide Diffs Side-by-Side Diffs Ignore Whitespace Patch

Changes to ext/misc/json.c.

    30     30   
    31     31   /* Unsigned integer types */
    32     32   typedef sqlite3_uint64 u64;
    33     33   typedef unsigned int u32;
    34     34   typedef unsigned char u8;
    35     35   
    36     36   /* Objects */
    37         -typedef struct Json Json;
           37  +typedef struct JsonString JsonString;
    38     38   typedef struct JsonNode JsonNode;
    39     39   typedef struct JsonParse JsonParse;
    40     40   
    41     41   /* An instance of this object represents a JSON string
    42     42   ** under construction.  Really, this is a generic string accumulator
    43     43   ** that can be and is used to create strings other than JSON.
    44     44   */
    45         -struct Json {
           45  +struct JsonString {
    46     46     sqlite3_context *pCtx;   /* Function context - put error messages here */
    47     47     char *zBuf;              /* Append JSON content here */
    48     48     u64 nAlloc;              /* Bytes of storage available in zBuf[] */
    49     49     u64 nUsed;               /* Bytes of zBuf[] currently used */
    50     50     u8 bStatic;              /* True if zBuf is static space */
    51     51     u8 bErr;                 /* True if an error has been encountered */
    52     52     char zSpace[100];        /* Initial static space */
................................................................................
    85     85     u8 eType;              /* One of the JSON_ type values */
    86     86     u8 jnFlags;            /* JNODE flags */
    87     87     u8 iVal;               /* Replacement value when JNODE_REPLACE */
    88     88     u32 n;                 /* Bytes of content, or number of sub-nodes */
    89     89     union {
    90     90       const char *zJContent; /* Content for INT, REAL, and STRING */
    91     91       u32 iAppend;           /* More terms for ARRAY and OBJECT */
           92  +    u32 iKey;              /* Key for ARRAY objects in json_tree() */
    92     93     } u;
    93     94   };
    94     95   
    95     96   /* A completely parsed JSON string
    96     97   */
    97     98   struct JsonParse {
    98     99     u32 nNode;         /* Number of slots of aNode[] used */
    99    100     u32 nAlloc;        /* Number of slots of aNode[] allocated */
   100    101     JsonNode *aNode;   /* Array of nodes containing the parse */
   101    102     const char *zJson; /* Original JSON string */
          103  +  u32 *aUp;          /* Index of parent of each node */
   102    104     u8 oom;            /* Set to true if out of memory */
   103    105   };
   104    106   
   105         -/*
   106         -** Return the number of consecutive JsonNode slots need to represent
   107         -** the parsed JSON at pNode.  The minimum answer is 1.  For ARRAY and
   108         -** OBJECT types, the number might be larger.
   109         -**
   110         -** Appended elements are not counted.  The value returned is the number
   111         -** by which the JsonNode counter should increment in order to go to the
   112         -** next peer value.
          107  +/**************************************************************************
          108  +** Utility routines for dealing with JsonString objects
          109  +**************************************************************************/
          110  +
          111  +/* Set the JsonString object to an empty string
   113    112   */
   114         -static u32 jsonSize(JsonNode *pNode){
   115         -  return pNode->eType>=JSON_ARRAY ? pNode->n+1 : 1;
   116         -}
   117         -
   118         -/* Set the Json object to an empty string
   119         -*/
   120         -static void jsonZero(Json *p){
          113  +static void jsonZero(JsonString *p){
   121    114     p->zBuf = p->zSpace;
   122    115     p->nAlloc = sizeof(p->zSpace);
   123    116     p->nUsed = 0;
   124    117     p->bStatic = 1;
   125    118   }
   126    119   
   127         -/* Initialize the Json object
          120  +/* Initialize the JsonString object
   128    121   */
   129         -static void jsonInit(Json *p, sqlite3_context *pCtx){
          122  +static void jsonInit(JsonString *p, sqlite3_context *pCtx){
   130    123     p->pCtx = pCtx;
   131    124     p->bErr = 0;
   132    125     jsonZero(p);
   133    126   }
   134    127   
   135    128   
   136         -/* Free all allocated memory and reset the Json object back to its
          129  +/* Free all allocated memory and reset the JsonString object back to its
   137    130   ** initial state.
   138    131   */
   139         -static void jsonReset(Json *p){
          132  +static void jsonReset(JsonString *p){
   140    133     if( !p->bStatic ) sqlite3_free(p->zBuf);
   141    134     jsonZero(p);
   142    135   }
   143    136   
   144    137   
   145    138   /* Report an out-of-memory (OOM) condition 
   146    139   */
   147         -static void jsonOom(Json *p){
          140  +static void jsonOom(JsonString *p){
   148    141     if( !p->bErr ){
   149    142       p->bErr = 1;
   150    143       sqlite3_result_error_nomem(p->pCtx);
   151    144       jsonReset(p);
   152    145     }
   153    146   }
   154    147   
   155    148   /* Enlarge pJson->zBuf so that it can hold at least N more bytes.
   156    149   ** Return zero on success.  Return non-zero on an OOM error
   157    150   */
   158         -static int jsonGrow(Json *p, u32 N){
          151  +static int jsonGrow(JsonString *p, u32 N){
   159    152     u64 nTotal = N<p->nAlloc ? p->nAlloc*2 : p->nAlloc+N+10;
   160    153     char *zNew;
   161    154     if( p->bStatic ){
   162    155       if( p->bErr ) return 1;
   163    156       zNew = sqlite3_malloc64(nTotal);
   164    157       if( zNew==0 ){
   165    158         jsonOom(p);
................................................................................
   176    169       }
   177    170       p->zBuf = zNew;
   178    171     }
   179    172     p->nAlloc = nTotal;
   180    173     return SQLITE_OK;
   181    174   }
   182    175   
   183         -/* Append N bytes from zIn onto the end of the Json string.
          176  +/* Append N bytes from zIn onto the end of the JsonString string.
   184    177   */
   185         -static void jsonAppendRaw(Json *p, const char *zIn, u32 N){
          178  +static void jsonAppendRaw(JsonString *p, const char *zIn, u32 N){
   186    179     if( (N+p->nUsed >= p->nAlloc) && jsonGrow(p,N)!=0 ) return;
   187    180     memcpy(p->zBuf+p->nUsed, zIn, N);
   188    181     p->nUsed += N;
   189    182   }
   190    183   
   191    184   #ifdef SQLITE_DEBUG
   192    185   /* Append the zero-terminated string zIn
   193    186   */
   194         -static void jsonAppend(Json *p, const char *zIn){
          187  +static void jsonAppend(JsonString *p, const char *zIn){
   195    188     jsonAppendRaw(p, zIn, (u32)strlen(zIn));
   196    189   }
   197    190   #endif
   198    191   
   199    192   /* Append a single character
   200    193   */
   201         -static void jsonAppendChar(Json *p, char c){
          194  +static void jsonAppendChar(JsonString *p, char c){
   202    195     if( p->nUsed>=p->nAlloc && jsonGrow(p,1)!=0 ) return;
   203    196     p->zBuf[p->nUsed++] = c;
   204    197   }
   205    198   
   206    199   /* Append a comma separator to the output buffer, if the previous
   207    200   ** character is not '[' or '{'.
   208    201   */
   209         -static void jsonAppendSeparator(Json *p){
          202  +static void jsonAppendSeparator(JsonString *p){
   210    203     char c;
   211    204     if( p->nUsed==0 ) return;
   212    205     c = p->zBuf[p->nUsed-1];
   213    206     if( c!='[' && c!='{' ) jsonAppendChar(p, ',');
   214    207   }
   215    208   
   216         -/* Append the N-byte string in zIn to the end of the Json string
          209  +/* Append the N-byte string in zIn to the end of the JsonString string
   217    210   ** under construction.  Enclose the string in "..." and escape
   218    211   ** any double-quotes or backslash characters contained within the
   219    212   ** string.
   220    213   */
   221         -static void jsonAppendString(Json *p, const char *zIn, u32 N){
          214  +static void jsonAppendString(JsonString *p, const char *zIn, u32 N){
   222    215     u32 i;
   223    216     if( (N+p->nUsed+2 >= p->nAlloc) && jsonGrow(p,N+2)!=0 ) return;
   224    217     p->zBuf[p->nUsed++] = '"';
   225    218     for(i=0; i<N; i++){
   226    219       char c = zIn[i];
   227    220       if( c=='"' || c=='\\' ){
   228    221         if( (p->nUsed+N+1-i > p->nAlloc) && jsonGrow(p,N+1-i)!=0 ) return;
................................................................................
   234    227   }
   235    228   
   236    229   /*
   237    230   ** Append a function parameter value to the JSON string under 
   238    231   ** construction.
   239    232   */
   240    233   static void jsonAppendValue(
   241         -  Json *p,                       /* Append to this JSON string */
          234  +  JsonString *p,                 /* Append to this JSON string */
   242    235     sqlite3_value *pValue          /* Value to append */
   243    236   ){
   244    237     switch( sqlite3_value_type(pValue) ){
   245    238       case SQLITE_NULL: {
   246    239         jsonAppendRaw(p, "null", 4);
   247    240         break;
   248    241       }
................................................................................
   269    262       }
   270    263     }
   271    264   }
   272    265   
   273    266   
   274    267   /* Make the JSON in p the result of the SQL function.
   275    268   */
   276         -static void jsonResult(Json *p){
          269  +static void jsonResult(JsonString *p){
   277    270     if( p->bErr==0 ){
   278    271       sqlite3_result_text64(p->pCtx, p->zBuf, p->nUsed, 
   279    272                             p->bStatic ? SQLITE_TRANSIENT : sqlite3_free,
   280    273                             SQLITE_UTF8);
   281    274       jsonZero(p);
   282    275     }
   283    276     assert( p->bStatic );
   284    277   }
          278  +
          279  +/**************************************************************************
          280  +** Utility routines for dealing with JsonNode and JsonParse objects
          281  +**************************************************************************/
          282  +
          283  +/*
          284  +** Return the number of consecutive JsonNode slots need to represent
          285  +** the parsed JSON at pNode.  The minimum answer is 1.  For ARRAY and
          286  +** OBJECT types, the number might be larger.
          287  +**
          288  +** Appended elements are not counted.  The value returned is the number
          289  +** by which the JsonNode counter should increment in order to go to the
          290  +** next peer value.
          291  +*/
          292  +static u32 jsonNodeSize(JsonNode *pNode){
          293  +  return pNode->eType>=JSON_ARRAY ? pNode->n+1 : 1;
          294  +}
          295  +
          296  +/*
          297  +** Reclaim all memory allocated by a JsonParse object.  But do not
          298  +** delete the JsonParse object itself.
          299  +*/
          300  +static void jsonParseReset(JsonParse *pParse){
          301  +  sqlite3_free(pParse->aNode);
          302  +  pParse->aNode = 0;
          303  +  pParse->nNode = 0;
          304  +  pParse->nAlloc = 0;
          305  +  sqlite3_free(pParse->aUp);
          306  +  pParse->aUp = 0;
          307  +}
   285    308   
   286    309   /*
   287    310   ** Convert the JsonNode pNode into a pure JSON string and
   288    311   ** append to pOut.  Subsubstructure is also included.  Return
   289    312   ** the number of JsonNode objects that are encoded.
   290    313   */
   291    314   static void jsonRenderNode(
   292    315     JsonNode *pNode,               /* The node to render */
   293         -  Json *pOut,                    /* Write JSON here */
          316  +  JsonString *pOut,              /* Write JSON here */
   294    317     sqlite3_value **aReplace       /* Replacement values */
   295    318   ){
   296    319     switch( pNode->eType ){
   297    320       case JSON_NULL: {
   298    321         jsonAppendRaw(pOut, "null", 4);
   299    322         break;
   300    323       }
................................................................................
   328    351                 jsonAppendSeparator(pOut);
   329    352                 jsonAppendValue(pOut, aReplace[pNode[j].iVal]);
   330    353               }
   331    354             }else{
   332    355               jsonAppendSeparator(pOut);
   333    356               jsonRenderNode(&pNode[j], pOut, aReplace);
   334    357             }
   335         -          j += jsonSize(&pNode[j]);
          358  +          j += jsonNodeSize(&pNode[j]);
   336    359           }
   337    360           if( (pNode->jnFlags & JNODE_APPEND)==0 ) break;
   338    361           pNode = &pNode[pNode->u.iAppend];
   339    362           j = 1;
   340    363         }
   341    364         jsonAppendChar(pOut, ']');
   342    365         break;
................................................................................
   352    375               jsonAppendChar(pOut, ':');
   353    376               if( pNode[j+1].jnFlags & JNODE_REPLACE ){
   354    377                 jsonAppendValue(pOut, aReplace[pNode[j+1].iVal]);
   355    378               }else{
   356    379                 jsonRenderNode(&pNode[j+1], pOut, aReplace);
   357    380               }
   358    381             }
   359         -          j += 1 + jsonSize(&pNode[j+1]);
          382  +          j += 1 + jsonNodeSize(&pNode[j+1]);
   360    383           }
   361    384           if( (pNode->jnFlags & JNODE_APPEND)==0 ) break;
   362    385           pNode = &pNode[pNode->u.iAppend];
   363    386           j = 1;
   364    387         }
   365    388         jsonAppendChar(pOut, '}');
   366    389         break;
................................................................................
   474    497           zOut[j] = 0;
   475    498           sqlite3_result_text(pCtx, zOut, j, sqlite3_free);
   476    499         }
   477    500         break;
   478    501       }
   479    502       case JSON_ARRAY:
   480    503       case JSON_OBJECT: {
   481         -      Json s;
          504  +      JsonString s;
   482    505         jsonInit(&s, pCtx);
   483    506         jsonRenderNode(pNode, &s, aReplace);
   484    507         jsonResult(&s);
   485    508         break;
   486    509       }
   487    510     }
   488    511   }
................................................................................
   672    695     pParse->zJson = zJson;
   673    696     i = jsonParseValue(pParse, 0);
   674    697     if( i>0 ){
   675    698       while( isspace(zJson[i]) ) i++;
   676    699       if( zJson[i] ) i = -1;
   677    700     }
   678    701     if( i<0 ){
   679         -    sqlite3_free(pParse->aNode);
   680         -    pParse->aNode = 0;
   681         -    pParse->nNode = 0;
   682         -    pParse->nAlloc = 0;
          702  +    jsonParseReset(pParse);
   683    703       return 1;
   684    704     }
   685    705     return 0;
   686    706   }
          707  +
          708  +/* Mark node i of pParse as being a child of iParent.  Call recursively
          709  +** to fill in all the descendants of node i.
          710  +*/
          711  +static void jsonParseFillInParentage(JsonParse *pParse, u32 i, u32 iParent){
          712  +  JsonNode *pNode = &pParse->aNode[i];
          713  +  u32 j;
          714  +  pParse->aUp[i] = iParent;
          715  +  switch( pNode->eType ){
          716  +    case JSON_ARRAY: {
          717  +      for(j=1; j<=pNode->n; j += jsonNodeSize(pNode+j)){
          718  +        jsonParseFillInParentage(pParse, i+j, i);
          719  +      }
          720  +      break;
          721  +    }
          722  +    case JSON_OBJECT: {
          723  +      for(j=1; j<=pNode->n; j += jsonNodeSize(pNode+j+1)+1){
          724  +        pParse->aUp[i+j] = i;
          725  +        jsonParseFillInParentage(pParse, i+j+1, i);
          726  +      }
          727  +      break;
          728  +    }
          729  +    default: {
          730  +      break;
          731  +    }
          732  +  }
          733  +}
          734  +
          735  +/*
          736  +** Compute the parentage of all nodes in a completed parse.
          737  +*/
          738  +static int jsonParseFindParents(JsonParse *pParse){
          739  +  u32 *aUp;
          740  +  assert( pParse->aUp==0 );
          741  +  aUp = pParse->aUp = sqlite3_malloc( sizeof(u32)*pParse->nNode );
          742  +  if( aUp==0 ) return SQLITE_NOMEM;
          743  +  jsonParseFillInParentage(pParse, 0, 0);
          744  +  return SQLITE_OK;
          745  +}
   687    746   
   688    747   /* forward declaration */
   689    748   static JsonNode *jsonLookupAppend(JsonParse*,const char*,int*);
   690    749   
   691    750   /*
   692    751   ** Search along zPath to find the node specified.  Return a pointer
   693    752   ** to that node, or NULL if zPath is malformed or if there is no such
................................................................................
   726    785         while( j<=pRoot->n ){
   727    786           if( pRoot[j].n==nKey+2
   728    787            && strncmp(&pRoot[j].u.zJContent[1],zKey,nKey)==0
   729    788           ){
   730    789             return jsonLookup(pParse, iRoot+j+1, &zPath[i], pApnd);
   731    790           }
   732    791           j++;
   733         -        j += jsonSize(&pRoot[j]);
          792  +        j += jsonNodeSize(&pRoot[j]);
   734    793         }
   735    794         if( (pRoot->jnFlags & JNODE_APPEND)==0 ) break;
   736    795         iRoot += pRoot->u.iAppend;
   737    796         pRoot = &pParse->aNode[iRoot];
   738    797         j = 1;
   739    798       }
   740    799       if( pApnd ){
................................................................................
   755    814         zPath++;
   756    815       }
   757    816       if( zPath[0]!=']' ) return 0;
   758    817       zPath++;
   759    818       j = 1;
   760    819       for(;;){
   761    820         while( i>0 && j<=pRoot->n ){
   762         -        j += jsonSize(&pRoot[j]);
          821  +        j += jsonNodeSize(&pRoot[j]);
   763    822           i--;
   764    823         }
   765    824         if( (pRoot->jnFlags & JNODE_APPEND)==0 ) break;
   766    825         iRoot += pRoot->u.iAppend;
   767    826         pRoot = &pParse->aNode[iRoot];
   768    827         j = 1;
   769    828       }
................................................................................
   816    875   ** well-formed.
   817    876   */
   818    877   static void jsonParseFunc(
   819    878     sqlite3_context *context,
   820    879     int argc,
   821    880     sqlite3_value **argv
   822    881   ){
   823         -  Json s;       /* Output string - not real JSON */
   824         -  JsonParse x;  /* The parse */
          882  +  JsonString s;       /* Output string - not real JSON */
          883  +  JsonParse x;        /* The parse */
   825    884     u32 i;
   826    885     char zBuf[100];
   827    886   
   828    887     assert( argc==1 );
   829    888     if( jsonParse(&x, (const char*)sqlite3_value_text(argv[0])) ) return;
   830    889     jsonInit(&s, context);
   831    890     for(i=0; i<x.nNode; i++){
................................................................................
   834    893       jsonAppend(&s, zBuf);
   835    894       if( x.aNode[i].u.zJContent!=0 ){
   836    895         jsonAppendRaw(&s, "    text: ", 10);
   837    896         jsonAppendRaw(&s, x.aNode[i].u.zJContent, x.aNode[i].n);
   838    897         jsonAppendRaw(&s, "\n", 1);
   839    898       }
   840    899     }
   841         -  sqlite3_free(x.aNode);
          900  +  jsonParseReset(&x);
   842    901     jsonResult(&s);
   843    902   }
   844    903   
   845    904   /*
   846    905   ** The json_test1(JSON) function parses and rebuilds the JSON string.
   847    906   */
   848    907   static void jsonTest1Func(
................................................................................
   849    908     sqlite3_context *context,
   850    909     int argc,
   851    910     sqlite3_value **argv
   852    911   ){
   853    912     JsonParse x;  /* The parse */
   854    913     if( jsonParse(&x, (const char*)sqlite3_value_text(argv[0])) ) return;
   855    914     jsonReturn(x.aNode, context, 0);
   856         -  sqlite3_free(x.aNode);
          915  +  jsonParseReset(&x);
   857    916   }
   858    917   
   859    918   /*
   860    919   ** The json_nodecount(JSON) function returns the number of nodes in the
   861    920   ** input JSON string.
   862    921   */
   863    922   static void jsonNodeCountFunc(
................................................................................
   864    923     sqlite3_context *context,
   865    924     int argc,
   866    925     sqlite3_value **argv
   867    926   ){
   868    927     JsonParse x;  /* The parse */
   869    928     if( jsonParse(&x, (const char*)sqlite3_value_text(argv[0])) ) return;
   870    929     sqlite3_result_int64(context, x.nNode);
   871         -  sqlite3_free(x.aNode);
          930  +  jsonParseReset(&x);
   872    931   }
   873    932   #endif /* SQLITE_DEBUG */
   874    933   
   875    934   /****************************************************************************
   876    935   ** SQL function implementations
   877    936   ****************************************************************************/
   878    937   
................................................................................
   883    942   */
   884    943   static void jsonArrayFunc(
   885    944     sqlite3_context *context,
   886    945     int argc,
   887    946     sqlite3_value **argv
   888    947   ){
   889    948     int i;
   890         -  Json jx;
          949  +  JsonString jx;
   891    950   
   892    951     jsonInit(&jx, context);
   893    952     jsonAppendChar(&jx, '[');
   894    953     for(i=0; i<argc; i++){
   895    954       jsonAppendSeparator(&jx);
   896    955       jsonAppendValue(&jx, argv[i]);
   897    956     }
................................................................................
   928    987     if( jsonParse(&x, (const char*)sqlite3_value_text(argv[0]))==0 ){
   929    988       if( x.nNode ){
   930    989         JsonNode *pNode = x.aNode;
   931    990         if( zPath ) pNode = jsonLookup(&x, 0, zPath, 0);
   932    991         if( pNode->eType==JSON_ARRAY ){
   933    992           assert( (pNode->jnFlags & JNODE_APPEND)==0 );
   934    993           for(i=1; i<=pNode->n; n++){
   935         -          i += jsonSize(&pNode[i]);
          994  +          i += jsonNodeSize(&pNode[i]);
   936    995           }
   937    996         }
   938    997       }
   939         -    sqlite3_free(x.aNode);
          998  +    jsonParseReset(&x);
   940    999     }
   941   1000     sqlite3_result_int64(context, n);
   942   1001   }
   943   1002   
   944   1003   /*
   945   1004   ** json_extract(JSON, PATH)
   946   1005   **
................................................................................
   961   1020     if( zPath[0]!='$' ) return;
   962   1021     zPath++;
   963   1022     if( jsonParse(&x, (const char*)sqlite3_value_text(argv[0])) ) return;
   964   1023     pNode = jsonLookup(&x, 0, zPath, 0);
   965   1024     if( pNode ){
   966   1025       jsonReturn(pNode, context, 0);
   967   1026     }
   968         -  sqlite3_free(x.aNode);
         1027  +  jsonParseReset(&x);
   969   1028   }
   970   1029   
   971   1030   /*
   972   1031   ** Implementation of the json_object(NAME,VALUE,...) function.  Return a JSON
   973   1032   ** object that contains all name/value given in arguments.  Or if any name
   974   1033   ** is not a string or if any value is a BLOB, throw an error.
   975   1034   */
   976   1035   static void jsonObjectFunc(
   977   1036     sqlite3_context *context,
   978   1037     int argc,
   979   1038     sqlite3_value **argv
   980   1039   ){
   981   1040     int i;
   982         -  Json jx;
         1041  +  JsonString jx;
   983   1042     const char *z;
   984   1043     u32 n;
   985   1044   
   986   1045     if( argc&1 ){
   987   1046       sqlite3_result_error(context, "json_object() requires an even number "
   988   1047                                     "of arguments", -1);
   989   1048       return;
................................................................................
  1035   1094         pNode = jsonLookup(&x, 0, &zPath[1], 0);
  1036   1095         if( pNode ) pNode->jnFlags |= JNODE_REMOVE;
  1037   1096       }
  1038   1097       if( (x.aNode[0].jnFlags & JNODE_REMOVE)==0 ){
  1039   1098         jsonReturn(x.aNode, context, 0);
  1040   1099       }
  1041   1100     }
  1042         -  sqlite3_free(x.aNode);
         1101  +  jsonParseReset(&x);
  1043   1102   }
  1044   1103   
  1045   1104   /*
  1046   1105   ** json_replace(JSON, PATH, VALUE, ...)
  1047   1106   **
  1048   1107   ** Replace the value at PATH with VALUE.  If PATH does not already exist,
  1049   1108   ** this routine is a no-op.  If JSON is ill-formed, return NULL.
................................................................................
  1078   1137       }
  1079   1138       if( x.aNode[0].jnFlags & JNODE_REPLACE ){
  1080   1139         sqlite3_result_value(context, argv[x.aNode[0].iVal]);
  1081   1140       }else{
  1082   1141         jsonReturn(x.aNode, context, argv);
  1083   1142       }
  1084   1143     }
  1085         -  sqlite3_free(x.aNode);
         1144  +  jsonParseReset(&x);
  1086   1145   }
         1146  +
  1087   1147   /*
  1088   1148   ** json_set(JSON, PATH, VALUE, ...)
  1089   1149   **
  1090   1150   ** Set the value at PATH to VALUE.  Create the PATH if it does not already
  1091   1151   ** exist.  Overwrite existing values that do exist.
  1092   1152   ** If JSON is ill-formed, return NULL.
  1093   1153   **
................................................................................
  1129   1189       }
  1130   1190       if( x.aNode[0].jnFlags & JNODE_REPLACE ){
  1131   1191         sqlite3_result_value(context, argv[x.aNode[0].iVal]);
  1132   1192       }else{
  1133   1193         jsonReturn(x.aNode, context, argv);
  1134   1194       }
  1135   1195     }
  1136         -  sqlite3_free(x.aNode);
         1196  +  jsonParseReset(&x);
  1137   1197   }
  1138   1198   
  1139   1199   /*
  1140   1200   ** json_type(JSON)
  1141   1201   ** json_type(JSON, PATH)
  1142   1202   **
  1143   1203   ** Return the top-level "type" of a JSON string.  Return NULL if the
................................................................................
  1161   1221     }
  1162   1222     if( jsonParse(&x, (const char*)sqlite3_value_text(argv[0])) ) return;
  1163   1223     if( x.nNode ){
  1164   1224       JsonNode *pNode = x.aNode;
  1165   1225       if( zPath ) pNode = jsonLookup(&x, 0, zPath, 0);
  1166   1226       sqlite3_result_text(context, jsonType[pNode->eType], -1, SQLITE_STATIC);
  1167   1227     }
  1168         -  sqlite3_free(x.aNode);
         1228  +  jsonParseReset(&x);
  1169   1229   }
  1170   1230   
  1171   1231   /****************************************************************************
  1172   1232   ** The json_each virtual table
  1173   1233   ****************************************************************************/
  1174   1234   typedef struct JsonEachCursor JsonEachCursor;
  1175   1235   struct JsonEachCursor {
  1176   1236     sqlite3_vtab_cursor base;  /* Base class - must be first */
  1177         -  u32 iRowid;         /* The rowid */
  1178         -  u32 i;              /* Index in sParse.aNode[] of current row */
  1179         -  u32 iEnd;           /* EOF when i equals or exceeds this value */
  1180         -  u8 eType;           /* Type of top-level element */
  1181         -  char *zJson;        /* Input json */
  1182         -  char *zPath;        /* Path by which to filter zJson */
  1183         -  JsonParse sParse;   /* The input json */
         1237  +  u32 iRowid;                /* The rowid */
         1238  +  u32 i;                     /* Index in sParse.aNode[] of current row */
         1239  +  u32 iEnd;                  /* EOF when i equals or exceeds this value */
         1240  +  u8 eType;                  /* Type of top-level element */
         1241  +  u8 bRecursive;             /* True for json_tree().  False for json_each() */
         1242  +  char *zJson;               /* Input JSON */
         1243  +  char *zPath;               /* Path by which to filter zJson */
         1244  +  JsonParse sParse;          /* Parse of the input JSON */
  1184   1245   };
  1185   1246   
  1186   1247   /* Constructor for the json_each virtual table */
  1187   1248   static int jsonEachConnect(
  1188   1249     sqlite3 *db,
  1189   1250     void *pAux,
  1190   1251     int argc, const char *const*argv,
  1191   1252     sqlite3_vtab **ppVtab,
  1192   1253     char **pzErr
  1193   1254   ){
  1194   1255     sqlite3_vtab *pNew;
  1195         -  pNew = *ppVtab = sqlite3_malloc( sizeof(*pNew) );
  1196         -  if( pNew==0 ) return SQLITE_NOMEM;
         1256  +  int rc;
  1197   1257   
  1198   1258   /* Column numbers */
  1199   1259   #define JEACH_KEY    0
  1200   1260   #define JEACH_VALUE  1
  1201         -#define JEACH_JSON   2
  1202         -#define JEACH_PATH   3
         1261  +#define JEACH_TYPE   2
         1262  +#define JEACH_ATOM   3
         1263  +#define JEACH_ID     4
         1264  +#define JEACH_PARENT 5
         1265  +#define JEACH_JSON   6
         1266  +#define JEACH_PATH   7
  1203   1267   
  1204         -  sqlite3_declare_vtab(db, "CREATE TABLE x(key,value,json hidden,path hidden)");
  1205         -  memset(pNew, 0, sizeof(*pNew));
  1206         -  return SQLITE_OK;
         1268  +  rc = sqlite3_declare_vtab(db, 
         1269  +     "CREATE TABLE x(key,value,type,atom,id,parent,json hidden,path hidden)");
         1270  +  if( rc==SQLITE_OK ){
         1271  +    pNew = *ppVtab = sqlite3_malloc( sizeof(*pNew) );
         1272  +    if( pNew==0 ) return SQLITE_NOMEM;
         1273  +    memset(pNew, 0, sizeof(*pNew));
         1274  +  }
         1275  +  return rc;
  1207   1276   }
  1208   1277   
  1209   1278   /* destructor for json_each virtual table */
  1210   1279   static int jsonEachDisconnect(sqlite3_vtab *pVtab){
  1211   1280     sqlite3_free(pVtab);
  1212   1281     return SQLITE_OK;
  1213   1282   }
  1214   1283   
  1215         -/* constructor for a JsonEachCursor object. */
  1216         -static int jsonEachOpen(sqlite3_vtab *p, sqlite3_vtab_cursor **ppCursor){
         1284  +/* constructor for a JsonEachCursor object for json_each(). */
         1285  +static int jsonEachOpenEach(sqlite3_vtab *p, sqlite3_vtab_cursor **ppCursor){
  1217   1286     JsonEachCursor *pCur;
  1218   1287     pCur = sqlite3_malloc( sizeof(*pCur) );
  1219   1288     if( pCur==0 ) return SQLITE_NOMEM;
  1220   1289     memset(pCur, 0, sizeof(*pCur));
  1221   1290     *ppCursor = &pCur->base;
  1222   1291     return SQLITE_OK;
  1223   1292   }
         1293  +
         1294  +/* constructor for a JsonEachCursor object for json_tree(). */
         1295  +static int jsonEachOpenTree(sqlite3_vtab *p, sqlite3_vtab_cursor **ppCursor){
         1296  +  int rc = jsonEachOpenEach(p, ppCursor);
         1297  +  if( rc==SQLITE_OK ){
         1298  +    JsonEachCursor *pCur = (JsonEachCursor*)*ppCursor;
         1299  +    pCur->bRecursive = 1;
         1300  +  }
         1301  +  return rc;
         1302  +}
  1224   1303   
  1225   1304   /* Reset a JsonEachCursor back to its original state.  Free any memory
  1226   1305   ** held. */
  1227   1306   static void jsonEachCursorReset(JsonEachCursor *p){
  1228   1307     sqlite3_free(p->zJson);
  1229   1308     sqlite3_free(p->zPath);
  1230         -  sqlite3_free(p->sParse.aNode);
         1309  +  jsonParseReset(&p->sParse);
  1231   1310     p->iRowid = 0;
  1232   1311     p->i = 0;
  1233   1312     p->iEnd = 0;
  1234   1313     p->eType = 0;
  1235         -  memset(&p->sParse, 0, sizeof(p->sParse));
  1236   1314     p->zJson = 0;
  1237   1315     p->zPath = 0;
  1238   1316   }
  1239   1317   
  1240   1318   /* Destructor for a jsonEachCursor object */
  1241   1319   static int jsonEachClose(sqlite3_vtab_cursor *cur){
  1242   1320     JsonEachCursor *p = (JsonEachCursor*)cur;
................................................................................
  1248   1326   /* Return TRUE if the jsonEachCursor object has been advanced off the end
  1249   1327   ** of the JSON object */
  1250   1328   static int jsonEachEof(sqlite3_vtab_cursor *cur){
  1251   1329     JsonEachCursor *p = (JsonEachCursor*)cur;
  1252   1330     return p->i >= p->iEnd;
  1253   1331   }
  1254   1332   
  1255         -/* Advance the cursor to the next top-level element of the current
  1256         -** JSON string */
  1257         -static int jsonEachNext(sqlite3_vtab_cursor *cur){
         1333  +/* Advance the cursor to the next element for json_tree() */
         1334  +static int jsonEachNextTree(sqlite3_vtab_cursor *cur){
         1335  +  JsonEachCursor *p = (JsonEachCursor*)cur;
         1336  +  if( p->i==0 ){
         1337  +    p->i = 1;
         1338  +  }else if( p->sParse.aNode[p->sParse.aUp[p->i]].eType==JSON_OBJECT ){
         1339  +    p->i += 2;
         1340  +  }else{
         1341  +    p->i++;
         1342  +  }
         1343  +  p->iRowid++;
         1344  +  if( p->i<p->sParse.nNode ){
         1345  +    JsonNode *pUp = &p->sParse.aNode[p->sParse.aUp[p->i]];
         1346  +    p->eType = pUp->eType;
         1347  +    if( pUp->eType==JSON_ARRAY ) pUp->u.iKey++;
         1348  +    if( p->sParse.aNode[p->i].eType==JSON_ARRAY ){
         1349  +      p->sParse.aNode[p->i].u.iKey = 0;
         1350  +    }
         1351  +  }
         1352  +  return SQLITE_OK;
         1353  +}
         1354  +
         1355  +/* Advance the cursor to the next element for json_each() */
         1356  +static int jsonEachNextEach(sqlite3_vtab_cursor *cur){
  1258   1357     JsonEachCursor *p = (JsonEachCursor*)cur;
  1259   1358     switch( p->eType ){
  1260   1359       case JSON_ARRAY: {
  1261         -      p->i += jsonSize(&p->sParse.aNode[p->i]);
         1360  +      p->i += jsonNodeSize(&p->sParse.aNode[p->i]);
  1262   1361         p->iRowid++;
  1263   1362         break;
  1264   1363       }
  1265   1364       case JSON_OBJECT: {
  1266         -      p->i += 1 + jsonSize(&p->sParse.aNode[p->i+1]);
         1365  +      p->i += 1 + jsonNodeSize(&p->sParse.aNode[p->i+1]);
  1267   1366         p->iRowid++;
  1268   1367         break;
  1269   1368       }
  1270   1369       default: {
  1271   1370         p->i = p->iEnd;
  1272   1371         break;
  1273   1372       }
................................................................................
  1278   1377   /* Return the value of a column */
  1279   1378   static int jsonEachColumn(
  1280   1379     sqlite3_vtab_cursor *cur,   /* The cursor */
  1281   1380     sqlite3_context *ctx,       /* First argument to sqlite3_result_...() */
  1282   1381     int i                       /* Which column to return */
  1283   1382   ){
  1284   1383     JsonEachCursor *p = (JsonEachCursor*)cur;
         1384  +  JsonNode *pThis = &p->sParse.aNode[p->i];
  1285   1385     switch( i ){
  1286   1386       case JEACH_KEY: {
  1287   1387         if( p->eType==JSON_OBJECT ){
  1288         -        jsonReturn(&p->sParse.aNode[p->i], ctx, 0);
  1289         -      }else{
  1290         -        sqlite3_result_int64(ctx, p->iRowid);
         1388  +        jsonReturn(pThis, ctx, 0);
         1389  +      }else if( p->eType==JSON_ARRAY ){
         1390  +        u32 iKey;
         1391  +        if( p->bRecursive ){
         1392  +          if( p->iRowid==0 ) break;
         1393  +          iKey = p->sParse.aNode[p->sParse.aUp[p->i]].u.iKey - 1;
         1394  +        }else{
         1395  +          iKey = p->iRowid;
         1396  +        }
         1397  +        sqlite3_result_int64(ctx, iKey);
  1291   1398         }
  1292   1399         break;
  1293   1400       }
  1294   1401       case JEACH_VALUE: {
  1295         -      if( p->eType==JSON_OBJECT ){
  1296         -        jsonReturn(&p->sParse.aNode[p->i+1], ctx, 0);
  1297         -      }else{
  1298         -        jsonReturn(&p->sParse.aNode[p->i], ctx, 0);
         1402  +      if( p->eType==JSON_OBJECT ) pThis++;
         1403  +      jsonReturn(pThis, ctx, 0);
         1404  +      break;
         1405  +    }
         1406  +    case JEACH_TYPE: {
         1407  +      if( p->eType==JSON_OBJECT ) pThis++;
         1408  +      sqlite3_result_text(ctx, jsonType[pThis->eType], -1, SQLITE_STATIC);
         1409  +      break;
         1410  +    }
         1411  +    case JEACH_ATOM: {
         1412  +      if( p->eType==JSON_OBJECT ) pThis++;
         1413  +      if( pThis->eType>=JSON_ARRAY ) break;
         1414  +      jsonReturn(pThis, ctx, 0);
         1415  +      break;
         1416  +    }
         1417  +    case JEACH_ID: {
         1418  +      sqlite3_result_int64(ctx, p->i + (p->eType==JSON_OBJECT));
         1419  +      break;
         1420  +    }
         1421  +    case JEACH_PARENT: {
         1422  +      if( p->i>0 && p->bRecursive ){
         1423  +        sqlite3_result_int64(ctx, p->sParse.aUp[p->i]);
  1299   1424         }
  1300   1425         break;
  1301   1426       }
  1302   1427       case JEACH_PATH: {
  1303   1428         const char *zPath = p->zPath;
  1304   1429         if( zPath==0 ) zPath = "$";
  1305   1430         sqlite3_result_text(ctx, zPath, -1, SQLITE_STATIC);
  1306   1431         break;
  1307   1432       }
  1308   1433       default: {
         1434  +      assert( i==JEACH_JSON );
  1309   1435         sqlite3_result_text(ctx, p->sParse.zJson, -1, SQLITE_STATIC);
  1310   1436         break;
  1311   1437       }
  1312   1438     }
  1313   1439     return SQLITE_OK;
  1314   1440   }
  1315   1441   
................................................................................
  1341   1467         case JEACH_JSON:   jsonIdx = i;    break;
  1342   1468         case JEACH_PATH:   pathIdx = i;    break;
  1343   1469         default:           /* no-op */     break;
  1344   1470       }
  1345   1471     }
  1346   1472     if( jsonIdx<0 ){
  1347   1473       pIdxInfo->idxNum = 0;
  1348         -    pIdxInfo->estimatedCost = (double)2000000000;
         1474  +    pIdxInfo->estimatedCost = 1e99;
  1349   1475     }else{
  1350         -    pIdxInfo->estimatedCost = (double)1;
         1476  +    pIdxInfo->estimatedCost = 1.0;
  1351   1477       pIdxInfo->aConstraintUsage[jsonIdx].argvIndex = 1;
  1352   1478       pIdxInfo->aConstraintUsage[jsonIdx].omit = 1;
  1353   1479       if( pathIdx<0 ){
  1354   1480         pIdxInfo->idxNum = 1;
  1355   1481       }else{
  1356   1482         pIdxInfo->aConstraintUsage[pathIdx].argvIndex = 2;
  1357   1483         pIdxInfo->aConstraintUsage[pathIdx].omit = 1;
................................................................................
  1380   1506       zPath = (const char*)sqlite3_value_text(argv[1]);
  1381   1507       if( zPath==0 || zPath[0]!='$' ) return SQLITE_OK;
  1382   1508     }
  1383   1509     n = sqlite3_value_bytes(argv[0]);
  1384   1510     p->zJson = sqlite3_malloc( n+1 );
  1385   1511     if( p->zJson==0 ) return SQLITE_NOMEM;
  1386   1512     memcpy(p->zJson, z, n+1);
  1387         -  if( jsonParse(&p->sParse, p->zJson) ){
         1513  +  if( jsonParse(&p->sParse, p->zJson) 
         1514  +   || (p->bRecursive && jsonParseFindParents(&p->sParse))
         1515  +  ){
  1388   1516       jsonEachCursorReset(p);
  1389   1517     }else{
  1390   1518       JsonNode *pNode;
  1391   1519       if( idxNum==3 ){
  1392   1520         n = sqlite3_value_bytes(argv[1]);
  1393   1521         p->zPath = sqlite3_malloc( n+1 );
  1394   1522         if( p->zPath==0 ) return SQLITE_NOMEM;
................................................................................
  1417   1545   static sqlite3_module jsonEachModule = {
  1418   1546     0,                         /* iVersion */
  1419   1547     0,                         /* xCreate */
  1420   1548     jsonEachConnect,           /* xConnect */
  1421   1549     jsonEachBestIndex,         /* xBestIndex */
  1422   1550     jsonEachDisconnect,        /* xDisconnect */
  1423   1551     0,                         /* xDestroy */
  1424         -  jsonEachOpen,              /* xOpen - open a cursor */
         1552  +  jsonEachOpenEach,          /* xOpen - open a cursor */
         1553  +  jsonEachClose,             /* xClose - close a cursor */
         1554  +  jsonEachFilter,            /* xFilter - configure scan constraints */
         1555  +  jsonEachNextEach,          /* xNext - advance a cursor */
         1556  +  jsonEachEof,               /* xEof - check for end of scan */
         1557  +  jsonEachColumn,            /* xColumn - read data */
         1558  +  jsonEachRowid,             /* xRowid - read data */
         1559  +  0,                         /* xUpdate */
         1560  +  0,                         /* xBegin */
         1561  +  0,                         /* xSync */
         1562  +  0,                         /* xCommit */
         1563  +  0,                         /* xRollback */
         1564  +  0,                         /* xFindMethod */
         1565  +  0,                         /* xRename */
         1566  +};
         1567  +
         1568  +/* The methods of the json_tree virtual table. */
         1569  +static sqlite3_module jsonTreeModule = {
         1570  +  0,                         /* iVersion */
         1571  +  0,                         /* xCreate */
         1572  +  jsonEachConnect,           /* xConnect */
         1573  +  jsonEachBestIndex,         /* xBestIndex */
         1574  +  jsonEachDisconnect,        /* xDisconnect */
         1575  +  0,                         /* xDestroy */
         1576  +  jsonEachOpenTree,          /* xOpen - open a cursor */
  1425   1577     jsonEachClose,             /* xClose - close a cursor */
  1426   1578     jsonEachFilter,            /* xFilter - configure scan constraints */
  1427         -  jsonEachNext,              /* xNext - advance a cursor */
         1579  +  jsonEachNextTree,          /* xNext - advance a cursor */
  1428   1580     jsonEachEof,               /* xEof - check for end of scan */
  1429   1581     jsonEachColumn,            /* xColumn - read data */
  1430   1582     jsonEachRowid,             /* xRowid - read data */
  1431   1583     0,                         /* xUpdate */
  1432   1584     0,                         /* xBegin */
  1433   1585     0,                         /* xSync */
  1434   1586     0,                         /* xCommit */
  1435   1587     0,                         /* xRollback */
  1436   1588     0,                         /* xFindMethod */
  1437   1589     0,                         /* xRename */
  1438   1590   };
  1439   1591   
         1592  +/****************************************************************************
         1593  +** The following routine is the only publically visible identifier in this
         1594  +** file.  Call the following routine in order to register the various SQL
         1595  +** functions and the virtual table implemented by this file.
         1596  +****************************************************************************/
  1440   1597   
  1441   1598   #ifdef _WIN32
  1442   1599   __declspec(dllexport)
  1443   1600   #endif
  1444   1601   int sqlite3_json_init(
  1445   1602     sqlite3 *db, 
  1446   1603     char **pzErrMsg, 
................................................................................
  1469   1626   #if SQLITE_DEBUG
  1470   1627       /* DEBUG and TESTING functions */
  1471   1628       { "json_parse",           1, 0,   jsonParseFunc         },
  1472   1629       { "json_test1",           1, 0,   jsonTest1Func         },
  1473   1630       { "json_nodecount",       1, 0,   jsonNodeCountFunc     },
  1474   1631   #endif
  1475   1632     };
         1633  +  static const struct {
         1634  +     const char *zName;
         1635  +     sqlite3_module *pModule;
         1636  +  } aMod[] = {
         1637  +    { "json_each",            &jsonEachModule               },
         1638  +    { "json_tree",            &jsonTreeModule               },
         1639  +  };
  1476   1640     SQLITE_EXTENSION_INIT2(pApi);
  1477   1641     (void)pzErrMsg;  /* Unused parameter */
  1478   1642     for(i=0; i<sizeof(aFunc)/sizeof(aFunc[0]) && rc==SQLITE_OK; i++){
  1479   1643       rc = sqlite3_create_function(db, aFunc[i].zName, aFunc[i].nArg,
  1480   1644                                    SQLITE_UTF8 | SQLITE_DETERMINISTIC, 
  1481   1645                                    (void*)&aFunc[i].flag,
  1482   1646                                    aFunc[i].xFunc, 0, 0);
  1483   1647     }
  1484         -  if( rc==SQLITE_OK ){
  1485         -    rc = sqlite3_create_module(db, "json_each", &jsonEachModule, 0);
         1648  +  for(i=0; i<sizeof(aMod)/sizeof(aMod[0]) && rc==SQLITE_OK; i++){
         1649  +    rc = sqlite3_create_module(db, aMod[i].zName, aMod[i].pModule, 0);
  1486   1650     }
  1487   1651     return rc;
  1488   1652   }