SQLite JSON query to count number of items in nested list

Question

I'm trying to query a database in which a column contains JSON data. I am using python and the json1 extension to pull the queries into my python code.

A sample of the data within the json inside the database is below.

    "perf": {
        "timestamp": 1575933555,
        "frame": 0,
        "type": "BEST",
        "azimuth": 0
    },
    "dots": [{
        "a": -1.6,
        "b": -6.4,
        "c": -0.1,
        "int": 72
    }, {
        "a": -1.9,
        "b": -6.4,
        "c": 0.0,
        "int": 60
    }]
}

I am attempting to count the number of items within the "dots" nested list. In this case there are two, although there could be more or less depending on the data within the row.

SELECT COUNT(json_extract(json, ("$.dots"))) FROM json_database;

Returns the total number of rows within the database. It does not dive into the "dots" list to count the number of items with it. Trying to index the $.dots[i] does not return the total number of elements either.

How does one count the total number of items within a nested list as sqlite query?

Answer 1

You missed seeing json_array_length() in the documentation. Try this:

SELECT json_array_length(json, '$.dots') AS count
FROM json_database;