How to check the type of *all* elements in a JSONArray within SQLite CHECK-constraint?

39 views Asked by At
  • This is the Table in SQlite:
CREATE TABLE document(
   id TEXT NOT NULL,
   data TEXT NOT NULL,
   CHECK ( json_type(data, '$.documentLines.title') IN ('text') ),
   PRIMARY KEY (id)
) STRICT;
  • This is the INSERT :
INSERT INTO document (id, data) VALUES ('1',
'{
    "documentLines": [
        {
            "title": "This is a nice text",
            "lineNumber": "1",
            "content": "bla bla bla"
        },
        {
            "title": "This is a nice text too",
            "lineNumber": "2",
            "content": "bla bla bla"
        },
        {
            "title": null,
            "lineNumber": "3",
            "content": "bla bla bla"
        }
    ]
}');

...because the 3. documentLine has "title": null, title is not 'text' and the INSERT should normally throw an [SQLITE_CONSTRAINT_CHECK] A CHECK constraint failed (CHECK constraint failed: json_type(data, '$.documentLines.title') IN ('text'), but it does not throw (--> the INSERT completes without exception) :(

  • The QUESTION is:

How to modify the CHECK-contraint (CHECK ( json_type(data, '$.documentLines.title') IN ('text') )) to make the INSERT throw an Exception?

  • I tried:

CHECK ( json_type(data, '$.documentLines.title') IN ('text') )

  • I expected:

[SQLITE_CONSTRAINT_CHECK] A CHECK constraint failed (CHECK constraint failed: json_type(data, '$.documentLines.title') IN ('text') <-- But did not throw!

0

There are 0 answers