3

I know that SQLite doesn't have a boolean data type, but for a JSON column I'd like to set the value to a real boolean instead of a 0/1 faux-boolean, but SQLite always seems to set it to an int.

Example:

sqlite> create table test (j text);
sqlite> insert into test values ('{"b":true}');

Make sure we've got valid JSON and extract the value:

sqlite> select json_valid(j) from test;
1

sqlite> select json_extract(j, '$.b') from tests;
1

Okay, so extracting a value converts it to a "native" SQLite data type, which makes sense and is documented in the json_extract() docs, but setting it also does, which is a bit annoying:

sqlite> select json_set(j, '$.b', true) from test;
{"b":1}

sqlite> select json_set(j, '$.b', 'true') from test;
{"b":"true"}

I can write some code in my application to recognize 0/1 as boolean and convert it, but I'd prefer not to just because one database migration needs to move some JSON fields from one column to the other as it introduces some complexities down the line (I need to change some types and then also modify some generic functions which currently accept only a bool type to be polymorphic).

Is there any way to set this to a real JSON boolean like {"b": true}?

I am using SQLite 3.34.1 (3.35 changelog doesn't mention anything about JSON, so I assume it's unchanged in the latest version).


My current workaround is to use a string replace after settings the values:

update test set j = replace(replace(j,
    '"b":0', '"b":false'),
    '"b":1', '"b":true');

But meh.

2
  • Maybe an alternative approach might be to accept that it's 0 or 1 and deal with this when migrating (to what system?) using CASE?
    – Vérace
    Mar 20, 2021 at 11:38
  • I'm just moving stuff from one column to the other, not migrating to another system @Vérace; for context, here is the full migration. I don't think any case can help me here, but maybe I'm missing something? Mar 22, 2021 at 2:24

1 Answer 1

4

To get 'true' be recognized as the actual JSON object true, you can use the json() function, for example

select json_set(j, '$.b', json('true')) from test;
{"b":true}

ref:

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.