Many hyperlinks are disabled.
Use anonymous login
to enable hyperlinks.
Changes In Branch json-enhancements Excluding Merge-Ins
This is equivalent to a diff from 2d6a16ca to 2027b3be
2022-01-09
| ||
21:16 | Update the json-enhancements.md document to match the version in the json-in-core branch, as outside readers have linked to this branch. (Leaf check-in: 2027b3be user: drh tags: json-enhancements) | |
2022-01-08
| ||
21:00 | Add function format() as an alias for printf(), for compatibility with other systems. (check-in: 68bffc61 user: drh tags: trunk) | |
15:05 | Improved commenting of changes in the json1.c extension. (check-in: 4d81425e user: drh tags: json-enhancements) | |
2022-01-07
| ||
14:58 | Add new binary operators "->" and "->>" to the parser that evaluate to 2-argument SQL functions by the same name. Add new "->" and "->>" functions to the JSON extension that are aliases for json_extract(). (check-in: c4e4e3a3 user: drh tags: json-enhancements) | |
2022-01-06
| ||
17:13 | Add the '-guard:cf' compiler option for Windows 10, per forum post 8d3b4ad694. (check-in: 2d6a16ca user: mistachkin tags: trunk) | |
2022-01-05
| ||
21:01 | Remove two NEVER() macros that can sometimes be true if the database is corrupt. dbsqlfuzz 0414d2c18290fc80fd5fb540def7d3e46c1ae9c6. (check-in: b6a82f3c user: drh tags: trunk) | |
Added doc/json-enhancements.md.
> > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > | 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 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 | # JSON Functions Enhancements (2022) This document summaries enhancements to the SQLite JSON support added in early 2022. ## 1.0 New feature summary: 1. New **->** and **->>** operators that work like MySQL and PostgreSQL (PG). 2. New functions: **json_nextract()** and **json_ntype()**. 3. JSON functions are built-in rather than being an extension. They are included by default, but can be omitted using the -DSQLITE_OMIT_JSON compile-time option. ## 2.0 The **json_nextract()** function. The new **json_nextract()** function works like **json_extract()** with one exception: if the input text in the first argument is not well-formed JSON, then json_nextract() returns NULL whereas json_extract() raises an error. The extra "n" in the name of json_nextract() can be throught of as meaning "null-if-error". A call to json_nextract($JSON,$PATH) is logically equivalent to: > ~~~ CASE WHEN json_valid($JSON) THEN json_extract($JSON,$PATH) END ~~~ The json_nextract() function is intended for use in tables where a column might hold a mixture of datatypes - some rows holding JSON and other rows holding primitive SQL datatypes such as INT, REAL, and TEXT. The json_nextract() function makes it easier to write robust queries against such tables. ## 3.0 New operators **->** and **->>** The SQLite language adds two new binary operators **->** and **->>**. The -> operator works like the two-argument version of json_nextract() and the ->> operator works like the two-argument version of json_extract(). The left-hand operand of -> and ->> is JSON. The right-hand operand is a JSON path expression. These operators extract and return a value from the left-hand JSON that is specified by right-hand path expression. The operators work exactly the same if the left-hand side is well-formed JSON. The only difference is that if the left-hand side is not well-formed JSON, the ->> raises an error whereas the -> operator simply returns NULL. ### 3.1 Compatibility with MySQL The ->> operator should be compatible with MySQL in the sense that a ->> operator that works in MySQL should work the same way in SQLite. But (see below) the SQLite ->> operator is also extended to support PG syntax so not every use of ->> that wworks in SQLite will work for MySQL. The -> operator is *mostly* compatible with MySQL. Key differences between the SQLite -> operator and the MySQL -> operator are: * The SQLite -> operator returns NULL if the left-hand side is not well-formed JSON whereas MySQL will raise an error. * When the JSON path expression on the right-hand side selects a text value from the JSON, the -> operator in MySQL returns the string quoted as if for JSON, whereas the SQLite -> operator returns an unquoted SQL text value. This second difference - the handling of text values extracted from JSON - is also a difference in the json_extract() function between SQLite and MySQL. Because json_extract() has been in active use for 6 years, and because the SQLite semantics seem to be more useful, there are no plans to change json_extract() to make it compatible with MySQL. ### 3.2 Compatibility with PostgreSQL (PG) The ->> operator in PG does not accept a JSON path expression as its right-hand operand. Instead, PG looks for either a text string X (which is then interpreted as the path "$.X") or an integer N (which is then interpreted as "$[N]"). In order to make the SQLite ->> operator compatible with the PG ->> operator, the SQLite ->> operator has been extended so that its right-hand operand can be either a text label or a integer array index, as it is in PG. The SQLite ->> operator also accepts full JSON path expressions as well. The enhancement of accepting JSON path expression that consist of just a bare object label or array index is unique to the -> and ->> operators. All other places in the SQLite JSON interface that require JSON path expressions continue to require well-formed JSON path expressions. Only -> and ->> accept the PG-compatible abbreviated path expressions. The -> operator in SQLite is *mostly* compatible with the -> operator in PG. The differences are the same as for MySQL. ## 4.0 The **json_ntype()** function. The **json_ntype()** function works like **json_type()** except that when the argument is not well-formed JSON, the json_ntype() function returns NULL whereas json_type() raises an error. The extra "n" in the name can be understood as standing for "null-if-error". The json_ntype($JSON) function is logically equivalent to: > ~~~ CASE WHEN json_valid($JSON) THEN json_type($JSON) END ~~~ The json_ntype() function can be seen as an enhanced version of the json_valid() function, that in addition to indicating whether or not the string is well-formed JSON, also indicates the top-level type of that JSON. ## 5.0 JSON moved into the core The JSON interface is now moved into the SQLite core. When originally written in 2015, the JSON functions were an extension that could be optionally included at compile-time, or loaded at run-time. The implementation was in a source file named ext/misc/json1.c in the source tree. JSON functions were only compiled in if the -DSQLITE_ENABLE_JSON1 compile-time option was used. After these enhancements, the JSON functions are now built-ins. The source file that implements the JSON functions is moved to src/json.c. No special compile-time options are needed to load JSON into the build. Instead, there is a new -DSQLITE_OMIT_JSON compile-time option to leave them out. |
Changes to ext/misc/json1.c.
︙ | ︙ | |||
1577 1578 1579 1580 1581 1582 1583 1584 1585 1586 | for(i=1; i<=pNode->n; n++){ i += jsonNodeSize(&pNode[i]); } } sqlite3_result_int64(ctx, n); } /* ** json_extract(JSON, PATH, ...) ** | > > > > > > > > > > > | > > > | > > | | > > > > > > > > > > > > > > > > < | | > > > > > > > > > > > > > > > > > > > > > > | > > > > > > > > > > > > > > > > > > > > > | | | | | < < < < | | | | | | > | 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 1619 1620 1621 1622 1623 1624 1625 1626 1627 1628 1629 1630 1631 1632 1633 1634 1635 1636 1637 1638 1639 1640 1641 1642 1643 1644 1645 1646 1647 1648 1649 1650 1651 1652 1653 1654 1655 1656 1657 1658 1659 1660 1661 1662 1663 1664 1665 1666 1667 1668 1669 1670 1671 1672 1673 1674 1675 1676 1677 1678 1679 1680 1681 1682 1683 1684 1685 1686 1687 1688 1689 1690 1691 1692 1693 1694 1695 1696 1697 1698 1699 1700 1701 1702 1703 1704 1705 1706 | for(i=1; i<=pNode->n; n++){ i += jsonNodeSize(&pNode[i]); } } sqlite3_result_int64(ctx, n); } /* ** Bit values for the flags passed into jsonExtractFunc() or ** jsonSetFunc() via the user-data value. */ #define JSON_NULLERR 0x01 /* Return NULL if input is not JSON */ #define JSON_ABPATH 0x02 /* Allow abbreviated JSON path specs */ #define JSON_ISSET 0x04 /* json_set(), not json_insert() */ /* ** json_extract(JSON, PATH, ...) ** json_nextract(JSON, PATH, ...) ** "->"(JSON,PATH) ** "->>"(JSON,PATH) ** ** Return the element described by PATH. Return NULL if that PATH element ** is not found. For leaf nodes of the JSON, the value returned is a pure ** SQL value. In other words, quotes have been removed from strings. ** ** If there are multiple PATHs, then the value returned is a JSON array ** with one entry in the array for each PATH term. ** ** Throw an error if any PATH is malformed. ** ** If JSON is not well-formed JSON then: ** ** (1) raise an error if the JSON_NULLERR flag is not set. ** ** (2) Otherwise (if the JSON_NULLERR flags is set and) if there ** is a single PATH argument with the value '$', simply quote ** the JSON input as if by json_quote(). In other words, treat ** the JSON input as a string and convert it into a valid JSON ** string. ** ** (3) Otherwise (if JSON_NULLERR is set and the PATH is not '$') ** return NULL ** ** If the JSON_ABPATH flag is set and there is only a single PATH, then ** allow abbreviated PATH specs that omit the leading "$". */ static void jsonExtractFunc( sqlite3_context *ctx, int argc, sqlite3_value **argv ){ JsonParse *p; /* The parse */ JsonNode *pNode; const char *zPath; int flags = *(int*)sqlite3_user_data(ctx); JsonString jx; if( argc<2 ) return; p = jsonParseCached(ctx, argv, (flags & JSON_NULLERR)!=0 ? 0 : ctx); if( p==0 ){ /* If the form is "json_nextract(IN,'$')" and IN is not well-formed JSON, ** then return IN as a quoted JSON string. */ if( (flags & JSON_NULLERR)!=0 && argc==2 && (zPath = (const char*)sqlite3_value_text(argv[1]))!=0 && zPath[0]=='$' && zPath[1]==0 ){ jsonQuoteFunc(ctx, argc, argv); } return; } if( argc==2 ){ /* With a single PATH argument, the return is the unquoted SQL value */ zPath = (const char*)sqlite3_value_text(argv[1]); if( zPath && zPath[0]!='$' && zPath[0]!=0 && (flags & JSON_ABPATH)!=0 ){ /* The -> and ->> operators accept abbreviated PATH arguments. This ** is mostly for compatibility with PostgreSQL, but also for convenience. ** ** NUMBER ==> $[NUMBER] // PG compatible ** LABEL ==> $.LABEL // PG compatible ** [NUMBER] ==> $[NUMBER] // Not PG. Purely for convenience */ jsonInit(&jx, ctx); if( safe_isdigit(zPath[0]) ){ jsonAppendRaw(&jx, "$[", 2); jsonAppendRaw(&jx, zPath, (int)strlen(zPath)); jsonAppendRaw(&jx, "]", 2); }else{ jsonAppendRaw(&jx, "$.", 1 + (zPath[0]!='[')); jsonAppendRaw(&jx, zPath, (int)strlen(zPath)); jsonAppendChar(&jx, 0); } pNode = jx.bErr ? 0 : jsonLookup(p, jx.zBuf, 0, ctx); jsonReset(&jx); }else{ pNode = jsonLookup(p, zPath, 0, ctx); } if( p->nErr ) return; if( pNode ) jsonReturn(pNode, ctx, 0); }else{ /* Two or more PATH arguments results in a JSON array with each ** element of the array being the value selected by one of the PATHs */ int i; jsonInit(&jx, ctx); jsonAppendChar(&jx, '['); for(i=1; i<argc; i++){ zPath = (const char*)sqlite3_value_text(argv[i]); pNode = jsonLookup(p, zPath, 0, ctx); if( p->nErr ) break; jsonAppendSeparator(&jx); if( pNode ){ jsonRenderNode(pNode, &jx, 0); }else{ jsonAppendRaw(&jx, "null", 4); } } if( i==argc ){ jsonAppendChar(&jx, ']'); jsonResult(&jx); sqlite3_result_subtype(ctx, JSON_SUBTYPE); } jsonReset(&jx); } } /* This is the RFC 7396 MergePatch algorithm. */ static JsonNode *jsonMergePatch( JsonParse *pParse, /* The JSON parser that contains the TARGET */ u32 iTarget, /* Node of the TARGET in pParse */ |
︙ | ︙ | |||
1853 1854 1855 1856 1857 1858 1859 1860 1861 1862 1863 1864 1865 1866 | sqlite3_result_value(ctx, argv[x.aNode[0].u.iReplace]); }else{ jsonReturnJson(x.aNode, ctx, argv); } replace_err: jsonParseReset(&x); } /* ** json_set(JSON, PATH, VALUE, ...) ** ** Set the value at PATH to VALUE. Create the PATH if it does not already ** exist. Overwrite existing values that do exist. ** If JSON or PATH is malformed, throw an error. | > | 1924 1925 1926 1927 1928 1929 1930 1931 1932 1933 1934 1935 1936 1937 1938 | sqlite3_result_value(ctx, argv[x.aNode[0].u.iReplace]); }else{ jsonReturnJson(x.aNode, ctx, argv); } replace_err: jsonParseReset(&x); } /* ** json_set(JSON, PATH, VALUE, ...) ** ** Set the value at PATH to VALUE. Create the PATH if it does not already ** exist. Overwrite existing values that do exist. ** If JSON or PATH is malformed, throw an error. |
︙ | ︙ | |||
1914 1915 1916 1917 1918 1919 1920 1921 1922 | } jsonSetDone: jsonParseReset(&x); } /* ** json_type(JSON) ** json_type(JSON, PATH) ** | > | | > > | | 1986 1987 1988 1989 1990 1991 1992 1993 1994 1995 1996 1997 1998 1999 2000 2001 2002 2003 2004 2005 2006 2007 2008 2009 2010 2011 2012 2013 2014 2015 2016 2017 | } jsonSetDone: jsonParseReset(&x); } /* ** json_type(JSON) ** json_ntype(JSON) ** json_type(JSON, PATH) ** ** Return the top-level "type" of a JSON string. json_type() raises an ** error if either the JSON or PATH inputs are not well-formed. json_ntype() ** works like the one-argument version of json_type() except that it ** returns NULL if the JSON argument is not well-formed. */ static void jsonTypeFunc( sqlite3_context *ctx, int argc, sqlite3_value **argv ){ JsonParse *p; /* The parse */ const char *zPath; JsonNode *pNode; p = jsonParseCached(ctx, argv, *(int*)sqlite3_user_data(ctx) ? 0 : ctx); if( p==0 ) return; if( argc==2 ){ zPath = (const char*)sqlite3_value_text(argv[1]); pNode = jsonLookup(p, zPath, 0, ctx); }else{ pNode = p->aNode; } |
︙ | ︙ | |||
2636 2637 2638 2639 2640 2641 2642 2643 2644 | ****************************************************************************/ int sqlite3Json1Init(sqlite3 *db){ int rc = SQLITE_OK; unsigned int i; static const struct { const char *zName; int nArg; int flag; | > < | | | | | > > > | | | | | | | | > | | | | | 2711 2712 2713 2714 2715 2716 2717 2718 2719 2720 2721 2722 2723 2724 2725 2726 2727 2728 2729 2730 2731 2732 2733 2734 2735 2736 2737 2738 2739 2740 2741 2742 2743 2744 2745 2746 2747 2748 2749 2750 2751 2752 | ****************************************************************************/ int sqlite3Json1Init(sqlite3 *db){ int rc = SQLITE_OK; unsigned int i; static const struct { const char *zName; void (*xFunc)(sqlite3_context*,int,sqlite3_value**); int nArg; int flag; } aFunc[] = { { "json", jsonRemoveFunc, 1, 0 }, { "json_array", jsonArrayFunc, -1, 0 }, { "json_array_length", jsonArrayLengthFunc, 1, 0 }, { "json_array_length", jsonArrayLengthFunc, 2, 0 }, { "json_extract", jsonExtractFunc, -1, 0 }, { "json_nextract", jsonExtractFunc, -1, JSON_NULLERR }, { "->", jsonExtractFunc, 2, JSON_NULLERR|JSON_ABPATH }, { "->>", jsonExtractFunc, 2, JSON_ABPATH }, { "json_insert", jsonSetFunc, -1, 0 }, { "json_object", jsonObjectFunc, -1, 0 }, { "json_patch", jsonPatchFunc, 2, 0 }, { "json_quote", jsonQuoteFunc, 1, 0 }, { "json_remove", jsonRemoveFunc, -1, 0 }, { "json_replace", jsonReplaceFunc, -1, 0 }, { "json_set", jsonSetFunc, -1, JSON_ISSET }, { "json_type", jsonTypeFunc, 1, 0 }, { "json_ntype", jsonTypeFunc, 1, JSON_NULLERR }, { "json_type", jsonTypeFunc, 2, 0 }, { "json_valid", jsonValidFunc, 1, 0 }, #if SQLITE_DEBUG /* DEBUG and TESTING functions */ { "json_parse", jsonParseFunc, 1, 0 }, { "json_test1", jsonTest1Func, 1, 0 }, #endif }; static const struct { const char *zName; int nArg; void (*xStep)(sqlite3_context*,int,sqlite3_value**); void (*xFinal)(sqlite3_context*); |
︙ | ︙ |
Changes to src/parse.y.
︙ | ︙ | |||
232 233 234 235 236 237 238 | // improve performance and reduce the executable size. The goal here is // to get the "jump" operations in ISNULL through ESCAPE to have numeric // values that are early enough so that all jump operations are clustered // at the beginning. // %token ABORT ACTION AFTER ANALYZE ASC ATTACH BEFORE BEGIN BY CASCADE CAST. %token CONFLICT DATABASE DEFERRED DESC DETACH EACH END EXCLUSIVE EXPLAIN FAIL. | | | 232 233 234 235 236 237 238 239 240 241 242 243 244 245 246 | // improve performance and reduce the executable size. The goal here is // to get the "jump" operations in ISNULL through ESCAPE to have numeric // values that are early enough so that all jump operations are clustered // at the beginning. // %token ABORT ACTION AFTER ANALYZE ASC ATTACH BEFORE BEGIN BY CASCADE CAST. %token CONFLICT DATABASE DEFERRED DESC DETACH EACH END EXCLUSIVE EXPLAIN FAIL. %token OR AND NOT IS MATCH LIKE_KW BETWEEN IN ISNULL NOTNULL NE EQ. %token GT LE LT GE ESCAPE. // The following directive causes tokens ABORT, AFTER, ASC, etc. to // fallback to ID if they will not parse as their original value. // This obviates the need for the "id" nonterminal. // %fallback ID |
︙ | ︙ | |||
282 283 284 285 286 287 288 | %right NOT. %left IS MATCH LIKE_KW BETWEEN IN ISNULL NOTNULL NE EQ. %left GT LE LT GE. %right ESCAPE. %left BITAND BITOR LSHIFT RSHIFT. %left PLUS MINUS. %left STAR SLASH REM. | | | 282 283 284 285 286 287 288 289 290 291 292 293 294 295 296 | %right NOT. %left IS MATCH LIKE_KW BETWEEN IN ISNULL NOTNULL NE EQ. %left GT LE LT GE. %right ESCAPE. %left BITAND BITOR LSHIFT RSHIFT. %left PLUS MINUS. %left STAR SLASH REM. %left CONCAT PTR. %left COLLATE. %right BITNOT. %nonassoc ON. // An IDENTIFIER can be a generic identifier, or one of several // keywords. Any non-standard keyword can also be an identifier. // |
︙ | ︙ | |||
1230 1231 1232 1233 1234 1235 1236 1237 1238 1239 1240 1241 1242 1243 | {A = sqlite3PExpr(pParse, @B, X, 0);/*A-overwrites-B*/} expr(A) ::= BITNOT(B) expr(X). {A = sqlite3PExpr(pParse, @B, X, 0);/*A-overwrites-B*/} expr(A) ::= PLUS|MINUS(B) expr(X). [BITNOT] { A = sqlite3PExpr(pParse, @B==TK_PLUS ? TK_UPLUS : TK_UMINUS, X, 0); /*A-overwrites-B*/ } %type between_op {int} between_op(A) ::= BETWEEN. {A = 0;} between_op(A) ::= NOT BETWEEN. {A = 1;} expr(A) ::= expr(A) between_op(N) expr(X) AND expr(Y). [BETWEEN] { ExprList *pList = sqlite3ExprListAppend(pParse,0, X); pList = sqlite3ExprListAppend(pParse,pList, Y); | > > > > > > | 1230 1231 1232 1233 1234 1235 1236 1237 1238 1239 1240 1241 1242 1243 1244 1245 1246 1247 1248 1249 | {A = sqlite3PExpr(pParse, @B, X, 0);/*A-overwrites-B*/} expr(A) ::= BITNOT(B) expr(X). {A = sqlite3PExpr(pParse, @B, X, 0);/*A-overwrites-B*/} expr(A) ::= PLUS|MINUS(B) expr(X). [BITNOT] { A = sqlite3PExpr(pParse, @B==TK_PLUS ? TK_UPLUS : TK_UMINUS, X, 0); /*A-overwrites-B*/ } expr(A) ::= expr(B) PTR(C) expr(D). { ExprList *pList = sqlite3ExprListAppend(pParse, 0, B); pList = sqlite3ExprListAppend(pParse, pList, D); A = sqlite3ExprFunction(pParse, pList, &C, 0); } %type between_op {int} between_op(A) ::= BETWEEN. {A = 0;} between_op(A) ::= NOT BETWEEN. {A = 1;} expr(A) ::= expr(A) between_op(N) expr(X) AND expr(Y). [BETWEEN] { ExprList *pList = sqlite3ExprListAppend(pParse,0, X); pList = sqlite3ExprListAppend(pParse,pList, Y); |
︙ | ︙ |
Changes to src/tokenize.c.
︙ | ︙ | |||
286 287 288 289 290 291 292 293 294 295 296 297 298 299 | return i; } case CC_MINUS: { if( z[1]=='-' ){ for(i=2; (c=z[i])!=0 && c!='\n'; i++){} *tokenType = TK_SPACE; /* IMP: R-22934-25134 */ return i; } *tokenType = TK_MINUS; return 1; } case CC_LP: { *tokenType = TK_LP; return 1; | > > > | 286 287 288 289 290 291 292 293 294 295 296 297 298 299 300 301 302 | return i; } case CC_MINUS: { if( z[1]=='-' ){ for(i=2; (c=z[i])!=0 && c!='\n'; i++){} *tokenType = TK_SPACE; /* IMP: R-22934-25134 */ return i; }else if( z[1]=='>' ){ *tokenType = TK_PTR; return 2 + (z[2]=='>'); } *tokenType = TK_MINUS; return 1; } case CC_LP: { *tokenType = TK_LP; return 1; |
︙ | ︙ |
Changes to test/func.test.
︙ | ︙ | |||
1502 1503 1504 1505 1506 1507 1508 1509 1510 | do_execsql_test func-35.110 { SELECT coalesce(x, 'xyz' LIKE printf('%.1000000c','y')) FROM t1; } {} do_execsql_test func-35.200 { CREATE TABLE t0(c0 CHECK(ABS(-9223372036854775808))); PRAGMA integrity_check; } {ok} finish_test | > > > > > > > > > > > > > > > | 1502 1503 1504 1505 1506 1507 1508 1509 1510 1511 1512 1513 1514 1515 1516 1517 1518 1519 1520 1521 1522 1523 1524 1525 | do_execsql_test func-35.110 { SELECT coalesce(x, 'xyz' LIKE printf('%.1000000c','y')) FROM t1; } {} do_execsql_test func-35.200 { CREATE TABLE t0(c0 CHECK(ABS(-9223372036854775808))); PRAGMA integrity_check; } {ok} # 2021-01-07: The -> and ->> operators. # proc ptr1 {a b} { return "$a->$b" } db func -> ptr1 proc ptr2 {a b} { return "$a->>$b" } db func ->> ptr2 do_execsql_test func-36.100 { SELECT 123 -> 456 } {123->456} do_execsql_test func-36.110 { SELECT 123 ->> 456 } {123->>456} finish_test |
Changes to test/json102.test.
︙ | ︙ | |||
67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 | } {{3}} do_execsql_test json102-240 { SELECT json_array_length('{"one":[1,2,3]}', '$.two'); } {{}} do_execsql_test json102-250 { SELECT json_extract('{"a":2,"c":[4,5,{"f":7}]}', '$'); } {{{"a":2,"c":[4,5,{"f":7}]}}} do_execsql_test json102-260 { SELECT json_extract('{"a":2,"c":[4,5,{"f":7}]}', '$.c'); } {{[4,5,{"f":7}]}} do_execsql_test json102-270 { SELECT json_extract('{"a":2,"c":[4,5,{"f":7}]}', '$.c[2]'); } {{{"f":7}}} do_execsql_test json102-280 { SELECT json_extract('{"a":2,"c":[4,5,{"f":7}]}', '$.c[2].f'); } {{7}} do_execsql_test json102-290 { SELECT json_extract('{"a":2,"c":[4,5],"f":7}','$.c','$.a'); } {{[[4,5],2]}} do_execsql_test json102-300 { SELECT json_extract('{"a":2,"c":[4,5,{"f":7}]}', '$.x'); } {{}} do_execsql_test json102-310 { SELECT json_extract('{"a":2,"c":[4,5,{"f":7}]}', '$.x', '$.a'); } {{[null,2]}} do_execsql_test json102-320 { SELECT json_insert('{"a":2,"c":4}', '$.a', 99); } {{{"a":2,"c":4}}} do_execsql_test json102-330 { | > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > | 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 | } {{3}} do_execsql_test json102-240 { SELECT json_array_length('{"one":[1,2,3]}', '$.two'); } {{}} do_execsql_test json102-250 { SELECT json_extract('{"a":2,"c":[4,5,{"f":7}]}', '$'); } {{{"a":2,"c":[4,5,{"f":7}]}}} do_execsql_test json102-251 { SELECT json_nextract('{"a":2,"c":[4,5,{"f":7}]}', '$'); } {{{"a":2,"c":[4,5,{"f":7}]}}} do_execsql_test json102-252 { SELECT '{"a":2,"c":[4,5,{"f":7}]}' -> '$'; } {{{"a":2,"c":[4,5,{"f":7}]}}} do_execsql_test json102-260 { SELECT json_extract('{"a":2,"c":[4,5,{"f":7}]}', '$.c'); } {{[4,5,{"f":7}]}} do_execsql_test json102-261 { SELECT '{"a":2,"c":[4,5,{"f":7}]}' -> '$.c'; } {{[4,5,{"f":7}]}} do_execsql_test json102-262 { SELECT '{"a":2,"c":[4,5,{"f":7}]}' -> 'c'; } {{[4,5,{"f":7}]}} do_catchsql_test json102-265 { SELECT json_extract('[1,2,3', '$[2]'); } {1 {malformed JSON}} do_catchsql_test json102-266 { SELECT json_nextract('[1,2,3', '$[2]'); } {0 {{}}} do_catchsql_test json102-267 { SELECT json_extract('[1,2,3', '$'); } {1 {malformed JSON}} do_catchsql_test json102-268 { SELECT json_nextract('[1,2,3', '$'); } {0 {{"[1,2,3"}}} do_catchsql_test json102-269a { SELECT '[1,2,3' ->> '$'; } {1 {malformed JSON}} do_catchsql_test json102-269b { SELECT '[1,2,3' -> '$'; } {0 {{"[1,2,3"}}} do_execsql_test json102-270 { SELECT json_extract('{"a":2,"c":[4,5,{"f":7}]}', '$.c[2]'); } {{{"f":7}}} do_execsql_test json102-271 { SELECT '{"a":2,"c":[4,5,{"f":7}]}' -> '$.c[2]'; } {{{"f":7}}} do_execsql_test json102-272 { SELECT '{"a":2,"c":[4,5,{"f":7}]}' -> 'c' -> 2; } {{{"f":7}}} do_execsql_test json102-280 { SELECT json_extract('{"a":2,"c":[4,5,{"f":7}]}', '$.c[2].f'); } {{7}} do_execsql_test json102-281 { SELECT '{"a":2,"c":[4,5,{"f":7}]}' -> 'c' -> 2 -> 'f'; } {{7}} do_execsql_test json102-282 { SELECT '{"a":2,"c":[4,5,{"f":7}]}' -> 'c' -> '[2]' -> 'f'; } {{7}} do_execsql_test json102-290 { SELECT json_extract('{"a":2,"c":[4,5],"f":7}','$.c','$.a'); } {{[[4,5],2]}} do_execsql_test json102-300 { SELECT json_extract('{"a":2,"c":[4,5,{"f":7}]}', '$.x'); } {{}} do_execsql_test json102-301 { SELECT '{"a":2,"c":[4,5,{"f":7}]}' -> 'x'; } {{}} do_execsql_test json102-302 { SELECT '{"a":2,"c":[4,5,{"f":7}]}' -> NULL; } {{}} do_execsql_test json102-310 { SELECT json_extract('{"a":2,"c":[4,5,{"f":7}]}', '$.x', '$.a'); } {{[null,2]}} do_execsql_test json102-320 { SELECT json_insert('{"a":2,"c":4}', '$.a', 99); } {{{"a":2,"c":4}}} do_execsql_test json102-330 { |
︙ | ︙ | |||
145 146 147 148 149 150 151 152 153 154 155 156 157 158 | } {{{"x":25}}} do_execsql_test json102-500 { SELECT json_remove('{"x":25,"y":42}','$'); } {{}} do_execsql_test json102-510 { SELECT json_type('{"a":[2,3.5,true,false,null,"x"]}'); } {{object}} do_execsql_test json102-520 { SELECT json_type('{"a":[2,3.5,true,false,null,"x"]}','$'); } {{object}} do_execsql_test json102-530 { SELECT json_type('{"a":[2,3.5,true,false,null,"x"]}','$.a'); } {{array}} do_execsql_test json102-540 { | > > > | 194 195 196 197 198 199 200 201 202 203 204 205 206 207 208 209 210 | } {{{"x":25}}} do_execsql_test json102-500 { SELECT json_remove('{"x":25,"y":42}','$'); } {{}} do_execsql_test json102-510 { SELECT json_type('{"a":[2,3.5,true,false,null,"x"]}'); } {{object}} do_execsql_test json102-511 { SELECT json_ntype('{"a":[2,3.5,true,false,null,"x"]}'); } {{object}} do_execsql_test json102-520 { SELECT json_type('{"a":[2,3.5,true,false,null,"x"]}','$'); } {{object}} do_execsql_test json102-530 { SELECT json_type('{"a":[2,3.5,true,false,null,"x"]}','$.a'); } {{array}} do_execsql_test json102-540 { |
︙ | ︙ | |||
178 179 180 181 182 183 184 185 186 187 188 189 190 191 | } {{}} do_execsql_test json102-610 { SELECT json_valid(char(123)||'"x":35'||char(125)); } {{1}} do_execsql_test json102-620 { SELECT json_valid(char(123)||'"x":35'); } {{0}} ifcapable vtab { do_execsql_test json102-1000 { CREATE TABLE user(name,phone); INSERT INTO user(name,phone) VALUES ('Alice','["919-555-2345","804-555-3621"]'), ('Bob','["201-555-8872"]'), | > > > > > > | 230 231 232 233 234 235 236 237 238 239 240 241 242 243 244 245 246 247 248 249 | } {{}} do_execsql_test json102-610 { SELECT json_valid(char(123)||'"x":35'||char(125)); } {{1}} do_execsql_test json102-620 { SELECT json_valid(char(123)||'"x":35'); } {{0}} do_catchsql_test json102-630 { SELECT json_type('["a",'); } {1 {malformed JSON}} do_catchsql_test json102-631 { SELECT json_ntype('["a",'); } {0 {{}}} ifcapable vtab { do_execsql_test json102-1000 { CREATE TABLE user(name,phone); INSERT INTO user(name,phone) VALUES ('Alice','["919-555-2345","804-555-3621"]'), ('Bob','["201-555-8872"]'), |
︙ | ︙ |