How to decode a JSON String which uses Integer values as Boolean, alternatively can I force SQLite to use true/false instead of 1/0 as Bool

I'm using SQLite with Swift to store some JSON in a table column. I convert a Codable object into a JSON String and store it in the "json" column in my "data" table.

id name json
1 item1 {"status" : true}
2 item2 {"status" : false}
struct Item : Codable {
    let status : Bool
//Decode with:
let decoder = JSONDecoder()
let item = try decoder.decode(Item.self, from: someStrigData)

The issue arises when I try to modify that stored JSON String using the SQLite JavaScript functions. SQLites uses 1 and 0 instead true/false and as a result, the JSON String fails to Decode.

To illustrate, the following SQL Query:

UPDATE data SET json = json_set(json, '$.status', false) WHERE name = "item1"

Will change the table the following way:

id name json
1 item1 {"status" : 0}
2 item2 {"status" : false}

I even tried to extract and use the Boolean value from hardcoded JSON String.

UPDATE data SET json = json_set(json, '$.status', json_extract('{"a": false}', '$.a') ) WHERE name = "item1"

But it did not help:

id name json
1 item1 {"status" : 0}
2 item2 {"status" : false}

I need a way to to force SQLite to use true/false instead of 0 and 1 OR I need a way for Swift to recognise 0 and 1 as true/false and correctly decode the JSON String into a Swift object.

I would like to note that the data here is for illustrative purposes and I actually use generics that conform to Codable, which means I can't implement custom encode/decode for each type because I want this to work for any object that conforms to Codable protocol.


If you're forced to deal with mixed types in the DB column, then you don't have any really good options directly via Codable. One possible solution is to use the JSONSerialization class to deserialize the incoming JSON into a dictionary, modify the appropriate leaf values to use boolean values consistently, re-serialize the modified data, then Swift-decode via Codable.

(You don't need the reverse transformation in this case, because you are apparently allowed to store true/false values in the DB as-is.)

If you can choose to adopt numeric values consistently in the DB column, you could perhaps use a computed property to work around the problem, something like:

struct Item : Codable {
    private let status : Int
    var itemStatus : Bool {
        status != 0
    init(itemStatus: Bool) {
        self.status = itemStatus ? 1 : 0

Hmm good ideas but I want to be able to use arbitrary Codable to store/retrieve/modify in the database without modifying each and every object type that I will store.

So I have an idea to use string operations in the data base, for example I can put a unique placeholder instead of true/false and then replace that placeholder together with the quotes using substring replacement functions to achieve the desired effect but it would be much nicer if there is way to solve it in Swift.

It appears that encoding/decoding needs a bit more flexibility.

Does using json(false) work?

UPDATE data SET json = json_set(json, '$.status', json(false)) WHERE name = "item1"
1 Like

If (and only if) it's not possible to fix the problem on the other side (so it always writes correct true / false), I'd do this:

struct JSBool: Codable {
    var value: Bool
    init(from decoder: Decoder) throws {
        let container = try decoder.singleValueContainer()
        if let value = try? container.decode(Bool.self) {
            self.value = value
        } else if let value = try? container.decode(Int.self) {
            self.value = value != 0 // treats any <> 0 as true
        } else {
            fatalError("Other case") // like "true", or "yes", etc

struct Item: Codable {
    var status: JSBool
    var boolStatus: Bool {
        get { status.value }
        set { status.value = newValue }

Note you'll have to change the use sites to "item.boolStatus" instead of "item.status".

1 Like

Thanks, it appears that the easiest way would be to fix it outside of Swift.

Unfortunately not, SQLite converts it to 1 or 0.

Edit: OH Okay, when you put it in quotes, like json('true'), it behaves correctly!

Thanks, I don't know how I missed that.


Ah I missed that on my post too. Sorry about that. I typed it out on my phone :grimacing:

Double check that the resting json has true/false without quotes:

{ "status": true }