SQLite json_array_length

user_14106021 0
Viewed 128

Trying to run a query on a column containing JSON data.

When I run the following:

SELECT json_array_length(threads.participants)
FROM threads

I get results of 2 and 119 (2 json arrays in most records, 119 in just one of them).

I tried another query using a CASE statement as the following:

SELECT
  CASE threads.participants
    WHEN json_array_length(threads.participants) = 2 THEN "2 Participants"
    ELSE "More than 2 Participants" 
  END AS "Number of Participants"
FROM threads

I get all NULL results for the second query. I expected to see the same number of results as the first query, just changed to "2 Participants" and "More than 2 Participants". What am I missing here?

case sqlite arrays json
1 Answers

The syntax you use in the CASE expression is wrong.
Try this:

SELECT CASE json_array_length(participants) 
         WHEN 2 THEN '2 Participants' 
         ELSE 'More than 2 Participants' 
       END AS "Number of Participants" 
FROM threads

Just to be on the safe side, you should include all possible cases (even if they don't currently exist):

SELECT CASE json_array_length(participants) 
         WHEN 0 THEN 'No Participants' 
         WHEN 1 THEN '1 Participant' 
         WHEN 2 THEN '2 Participants' 
         ELSE 'More than 2 Participants' 
       END AS "Number of Participants" 
FROM threads
user_104988280
Related