sqllite filter where json column is empty

Question

I have a sqlite3 db, where the entries look like so:

66241258-b554-4d11-8741-67623c71d154|["67c3a9cd-b3d7-4c09-9df1-6d1b7fc2df7b"]
caec4548-cc8b-40f9-863c-376bed779a37|["5c30bb81-2f15-45b3-b809-746763d22d57"]
f30098c1-99f6-4f5b-96fd-f0d3b773221e|[]
6d256a61-d878-4815-aee8-f4c0bc355c4b|["27deff96-9376-492a-9e4f-f703d9845bf0"]
3b0a5d8e-5194-4957-ae0a-632f98b8aa35|["27deff96-9376-492a-9e4f-f703d9845bf0"]
a1a797de-9155-4f77-9459-d65e16ff5f39|[]
519b4aaf-7ed5-49d8-b9a3-5846acd96dac|[]
dba391e0-136d-43d5-8b3a-5866c1b9896d|[]
cb482fa0-a2ba-4f80-b298-815a23f764b9|[]
2039b6dd-9f45-4033-b1bb-ff791f3e4ca4|["45e222b8-ba9d-4565-baf8-ed3615de787d"]
407d04a1-eac1-4475-b52f-99d9c012371e|[]
32ff0a18-e18c-478a-89cc-c044ab47ecc7|[]
c3563601-0256-454b-b7c0-eeb347a7a7be|[]
bfe80f03-a8f8-4258-ab17-a9b39fbf0611|[]
24774799-da1d-485b-9081-3ef29ccde172|[]
d56dd727-f7f8-4c9a-b7e1-a31e0b08a063|[]
09b1b368-fe6b-454a-a356-3260d33000d4|[]

I would like to filter out columns where the json is [].

At the end, I just want this:

66241258-b554-4d11-8741-67623c71d154|["67c3a9cd-b3d7-4c09-9df1-6d1b7fc2df7b"]
caec4548-cc8b-40f9-863c-376bed779a37|["5c30bb81-2f15-45b3-b809-746763d22d57"]
6d256a61-d878-4815-aee8-f4c0bc355c4b|["27deff96-9376-492a-9e4f-f703d9845bf0"]
3b0a5d8e-5194-4957-ae0a-632f98b8aa35|["27deff96-9376-492a-9e4f-f703d9845bf0"]
2039b6dd-9f45-4033-b1bb-ff791f3e4ca4|["45e222b8-ba9d-4565-baf8-ed3615de787d"]

currently, I use:

select * from results_json;

but I need something like:

select * from results_json where json_length!=0;

I am not sure what is the right sqlite3 syntax for this :(

score 1 · Accepted Answer · answered Apr 11 '23 at 14:04

1

You can use json_array_length to get the number of elements in a given JSON array.

select *
from results_json
where json_array_length(myjson) > 0

answered Apr 11 '23 at 14:04

SelVazi

10,028
2
13
29

Is it possible to get a count of these objects aswell? – JohnJ Apr 11 '23 at 15:34
1

You can check it here https://dbfiddle.uk/1hGkYQx7 – SelVazi Apr 11 '23 at 15:39

score 0 · Answer 2 · answered Apr 11 '23 at 13:26

You can check the length of your json columns entries with:

select * from tab where length(COLUMN_NAME_JSON_DATA) > 2;

Note: since your empty json is still a string/text (or BLOB) with the contents [] the length is 2 not 0.

As a full example (SQLite version 3.20.1):

sqlite> create table tab (id, json);
sqlite> insert into tab values ('2039b6dd-9f45-4033-b1bb-ff791f3e4ca4','["45e222b8-ba9d-4565-baf8-ed3615de787d"]');
sqlite> insert into tab values ('2039b6dd-9f45-4033-b1bb-ff791f3eaaa4','[]');
sqlite> select * from tab;
2039b6dd-9f45-4033-b1bb-ff791f3e4ca4|["45e222b8-ba9d-4565-baf8-ed3615de787d"]
2039b6dd-9f45-4033-b1bb-ff791f3eaaa4|[]
sqlite> select * from tab where length(json) > 2;
2039b6dd-9f45-4033-b1bb-ff791f3e4ca4|["45e222b8-ba9d-4565-baf8-ed3615de787d"]

sqllite filter where json column is empty

2 Answers2