xref: /sqlite-3.40.0/test/json104.test (revision e229ca03) 
1# 2017-03-22
2#
3# The author disclaims copyright to this source code.  In place of
4# a legal notice, here is a blessing:
5#
6#    May you do good and not evil.
7#    May you find forgiveness for yourself and forgive others.
8#    May you share freely, never taking more than you give.
9#
10#***********************************************************************
11# This file implements tests for json_patch(A,B) SQL function.
12#
13
14set testdir [file dirname $argv0]
15source $testdir/tester.tcl
16set testprefix json104
17
18# This is the example from pages 2 and 3 of RFC-7396
19do_execsql_test json104-100 {
20  SELECT json_patch('{
21       "a": "b",
22       "c": {
23         "d": "e",
24         "f": "g"
25       }
26     }','{
27       "a":"z",
28       "c": {
29         "f": null
30       }
31     }');
32} {{{"a":"z","c":{"d":"e"}}}}
33
34
35# This is the example from pages 4 and 5 of RFC-7396
36do_execsql_test json104-110 {
37  SELECT json_patch('{
38       "title": "Goodbye!",
39       "author" : {
40         "givenName" : "John",
41         "familyName" : "Doe"
42       },
43       "tags":[ "example", "sample" ],
44       "content": "This will be unchanged"
45     }','{
46       "title": "Hello!",
47       "phoneNumber": "+01-123-456-7890",
48       "author": {
49         "familyName": null
50       },
51       "tags": [ "example" ]
52     }');
53} {{{"title":"Hello!","author":{"givenName":"John"},"tags":["example"],"content":"This will be unchanged","phoneNumber":"+01-123-456-7890"}}}
54
55do_execsql_test json104-200 {
56  SELECT json_patch('[1,2,3]','{"x":null}');
57} {{{}}}
58do_execsql_test json104-210 {
59  SELECT json_patch('[1,2,3]','{"x":null,"y":1,"z":null}');
60} {{{"y":1}}}
61do_execsql_test json104-220 {
62  SELECT json_patch('{}','{"a":{"bb":{"ccc":null}}}');
63} {{{"a":{"bb":{}}}}}
64do_execsql_test json104-221 {
65  SELECT json_patch('{}','{"a":{"bb":{"ccc":[1,null,3]}}}');
66} {{{"a":{"bb":{"ccc":[1,null,3]}}}}}
67do_execsql_test json104-222 {
68  SELECT json_patch('{}','{"a":{"bb":{"ccc":[1,{"dddd":null},3]}}}');
69} {{{"a":{"bb":{"ccc":[1,{"dddd":null},3]}}}}}
70
71# Example test cases at the end of the RFC-7396 document
72do_execsql_test json104-300 {
73  SELECT json_patch('{"a":"b"}','{"a":"c"}');
74} {{{"a":"c"}}}
75do_execsql_test json104-300a {
76  SELECT coalesce(json_patch(null,'{"a":"c"}'), 'real-null');
77} {{real-null}}
78do_execsql_test json104-301 {
79  SELECT json_patch('{"a":"b"}','{"b":"c"}');
80} {{{"a":"b","b":"c"}}}
81do_execsql_test json104-302 {
82  SELECT json_patch('{"a":"b"}','{"a":null}');
83} {{{}}}
84do_execsql_test json104-303 {
85  SELECT json_patch('{"a":"b","b":"c"}','{"a":null}');
86} {{{"b":"c"}}}
87do_execsql_test json104-304 {
88  SELECT json_patch('{"a":["b"]}','{"a":"c"}');
89} {{{"a":"c"}}}
90do_execsql_test json104-305 {
91  SELECT json_patch('{"a":"c"}','{"a":["b"]}');
92} {{{"a":["b"]}}}
93do_execsql_test json104-306 {
94  SELECT json_patch('{"a":{"b":"c"}}','{"a":{"b":"d","c":null}}');
95} {{{"a":{"b":"d"}}}}
96do_execsql_test json104-307 {
97  SELECT json_patch('{"a":[{"b":"c"}]}','{"a":[1]}');
98} {{{"a":[1]}}}
99do_execsql_test json104-308 {
100  SELECT json_patch('["a","b"]','["c","d"]');
101} {{["c","d"]}}
102do_execsql_test json104-309 {
103  SELECT json_patch('{"a":"b"}','["c"]');
104} {{["c"]}}
105do_execsql_test json104-310 {
106  SELECT json_patch('{"a":"foo"}','null');
107} {{null}}
108do_execsql_test json104-310a {
109  SELECT coalesce(json_patch('{"a":"foo"}',null), 'real-null');
110} {{real-null}}
111do_execsql_test json104-311 {
112  SELECT json_patch('{"a":"foo"}','"bar"');
113} {{"bar"}}
114do_execsql_test json104-312 {
115  SELECT json_patch('{"e":null}','{"a":1}');
116} {{{"e":null,"a":1}}}
117do_execsql_test json104-313 {
118  SELECT json_patch('[1,2]','{"a":"b","c":null}');
119} {{{"a":"b"}}}
120do_execsql_test json104-314 {
121  SELECT json_patch('{}','{"a":{"bb":{"ccc":null}}}');
122} {{{"a":{"bb":{}}}}}
123do_execsql_test json104-320 {
124  SELECT json_patch('{"x":{"one":1}}','{"x":{"two":2},"x":"three"}');
125} {{{"x":"three"}}}
126
127#-------------------------------------------------------------------------
128
129do_execsql_test 401 {
130  CREATE TABLE obj(x);
131  INSERT INTO obj VALUES('{"a":1,"b":2}');
132  SELECT * FROM obj;
133} {{{"a":1,"b":2}}}
134do_execsql_test 402 {
135  UPDATE obj SET x = json_insert(x, '$.c', 3);
136  SELECT * FROM obj;
137} {{{"a":1,"b":2,"c":3}}}
138do_execsql_test 403 {
139  SELECT json_extract(x, '$.b') FROM obj;
140  SELECT json_extract(x, '$."b"') FROM obj;
141} {2 2}
142do_execsql_test 404 {
143  UPDATE obj SET x = json_set(x, '$."b"', 555);
144  SELECT json_extract(x, '$.b') FROM obj;
145  SELECT json_extract(x, '$."b"') FROM obj;
146} {555 555}
147do_execsql_test 405 {
148  UPDATE obj SET x = json_set(x, '$."d"', 4);
149  SELECT json_extract(x, '$."d"') FROM obj;
150} {4}
151
152
153finish_test
154