In SQLite, json_each()
is a table-valued function that walks the JSON value provided as its first argument and returns a table consisting of one row for each array element or object member.
We provide the JSON value as an argument when we call the function.
We can optionally pass a second argument, which specifies a path to start from. When we do this, json_each()
treats that path as the top-level element.
The json_each()
function only walks the immediate children of the top-level array or object, or just the top-level element itself if the top-level element is a primitive value. To recursively walk through the JSON substructure, use json_tree()
instead.
Syntax
We can use the function in the following ways:
json_each(X)
json_each(X,P)
Where X
represents the JSON, and P
is an optional argument that represents the path to treat as the top-level.
Example
Here’s an example to demonstrate how it works:
SELECT * FROM json_each('{ "name" : "Woof", "age" : 10 }');
Result:
+------+-------+---------+------+----+--------+---------+------+ | key | value | type | atom | id | parent | fullkey | path | +------+-------+---------+------+----+--------+---------+------+ | name | Woof | text | Woof | 2 | null | $.name | $ | | age | 10 | integer | 10 | 4 | null | $.age | $ | +------+-------+---------+------+----+--------+---------+------+
We can see that each object member has its own row with some useful information, such as its type (SQL text value), path, etc.
Regarding the id
column, according to the SQLite documentation this is an internal housekeeping number, the computation of which might change in future releases. The only guarantee is that the id
column will be different for every row.
The parent column is always null
when calling json_each()
. This column becomes more meaningful when using json_tree()
.
Array
In this example, the JSON value is an array:
SELECT * FROM json_each('[ 10, 30, 45 ]');
Result:
+-----+-------+---------+------+----+--------+---------+------+ | key | value | type | atom | id | parent | fullkey | path | +-----+-------+---------+------+----+--------+---------+------+ | 0 | 10 | integer | 10 | 1 | null | $[0] | $ | | 1 | 30 | integer | 30 | 2 | null | $[1] | $ | | 2 | 45 | integer | 45 | 3 | null | $[2] | $ | +-----+-------+---------+------+----+--------+---------+------+
Specify a Path
We can use a second argument to specify a path to treat as the top-level.
Example:
SELECT * FROM json_each('{ "a" : 1, "b" : [ 4, 7, 8 ] }', '$.b');
Result:
+-----+-------+---------+------+----+--------+---------+------+ | key | value | type | atom | id | parent | fullkey | path | +-----+-------+---------+------+----+--------+---------+------+ | 0 | 4 | integer | 4 | 5 | null | $.b[0] | $.b | | 1 | 7 | integer | 7 | 6 | null | $.b[1] | $.b | | 2 | 8 | integer | 8 | 7 | null | $.b[2] | $.b | +-----+-------+---------+------+----+--------+---------+------+
Larger Document
In this example we’ll use a larger JSON document. First, let’s call json_each()
without specifying a path:
SELECT * FROM json_each('[
{
"user" : "Spike",
"age" : 30,
"scores" : [ 9, 7, 3 ]
},
{
"user" : "Faye",
"age" : 25,
"scores" : [ 90, 87, 93 ]
},
{
"user" : "Jet",
"age" : 40,
"scores" : [ 50, 38, 67 ]
}
]'
);
Result:
+-----+----------------------------------------------+--------+------+----+--------+---------+------+ | key | value | type | atom | id | parent | fullkey | path | +-----+----------------------------------------------+--------+------+----+--------+---------+------+ | 0 | {"user":"Spike","age":30,"scores":[9,7,3]} | object | N/A | 1 | N/A | $[0] | $ | | 1 | {"user":"Faye","age":25,"scores":[90,87,93]} | object | N/A | 11 | N/A | $[1] | $ | | 2 | {"user":"Jet","age":40,"scores":[50,38,67]} | object | N/A | 21 | N/A | $[2] | $ | +-----+----------------------------------------------+--------+------+----+--------+---------+------+
In this case, our JSON value is an array that contains three objects. Each object is listed in the results.
Now, let’s call json_each()
again, but this time we’ll specify a path:
SELECT * FROM json_each('[
{
"user" : "Spike",
"age" : 30,
"scores" : [ 9, 7, 3 ]
},
{
"user" : "Faye",
"age" : 25,
"scores" : [ 90, 87, 93 ]
},
{
"user" : "Jet",
"age" : 40,
"scores" : [ 50, 38, 67 ]
}
]',
'$[1]'
);
Result:
+--------+------------+---------+------+----+--------+-------------+------+ | key | value | type | atom | id | parent | fullkey | path | +--------+------------+---------+------+----+--------+-------------+------+ | user | Faye | text | Faye | 13 | null | $[1].user | $[1] | | age | 25 | integer | 25 | 15 | null | $[1].age | $[1] | | scores | [90,87,93] | array | null | 17 | null | $[1].scores | $[1] | +--------+------------+---------+------+----+--------+-------------+------+
In this case I chose the second array element by specifying [1]
(arrays are zero based in SQLite).
The result is that the output contains information about the second array element.
This time we can see that the path
column contains $[1]
.
Let’s go deeper:
SELECT * FROM json_each('[
{
"user" : "Spike",
"age" : 30,
"scores" : [ 9, 7, 3 ]
},
{
"user" : "Faye",
"age" : 25,
"scores" : [ 90, 87, 93 ]
},
{
"user" : "Jet",
"age" : 40,
"scores" : [ 50, 38, 67 ]
}
]',
'$[1].scores'
);
Result:
+-----+-------+---------+------+----+--------+----------------+-------------+ | key | value | type | atom | id | parent | fullkey | path | +-----+-------+---------+------+----+--------+----------------+-------------+ | 0 | 90 | integer | 90 | 18 | null | $[1].scores[0] | $[1].scores | | 1 | 87 | integer | 87 | 19 | null | $[1].scores[1] | $[1].scores | | 2 | 93 | integer | 93 | 20 | null | $[1].scores[2] | $[1].scores | +-----+-------+---------+------+----+--------+----------------+-------------+
Now we get a row for each element in the scores
array.
Filtering the Query
We can modify our query to filter the results based on a given criteria. For example:
SELECT
fullkey,
value
FROM json_each('[
{
"user" : "Spike",
"age" : 30,
"scores" : [ 9, 7, 3 ]
},
{
"user" : "Faye",
"age" : 25,
"scores" : [ 90, 87, 93 ]
},
{
"user" : "Jet",
"age" : 40,
"scores" : [ 50, 38, 67 ]
}
]'
)
WHERE json_each.value LIKE '%Faye%';
Result:
+---------+----------------------------------------------+ | fullkey | value | +---------+----------------------------------------------+ | $[1] | {"user":"Faye","age":25,"scores":[90,87,93]} | +---------+----------------------------------------------+
A Database Example
Suppose we have the following table:
SELECT * FROM guests;
Result:
+-------+--------------------------------------------------+ | guest | lunch | +-------+--------------------------------------------------+ | Zohan | ["Beef Pie", "Fruit Salad", "Apple Juice"] | | Amy | ["Vegetable Quiche", "Apple", "Fruit Juice"] | | Rohit | ["Beef Curry", "Dragonfruit", "Vegetable Juice"] | | Igor | ["Chicken Pie", "Jackfruit", "Fruit Juice"] | | Stacy | ["Chicken Curry", "Fruit Salad", "Apple Juice"] | | Aisha | ["Chicken Curry", "Apple Pie", "Apple Juice"] | +-------+--------------------------------------------------+
This table called guests
has two columns. The first column contains the name of the guest, and the second column contains their lunch order. They can order three dishes for lunch. Their lunch order is in the form of an array, where each dish is an element in the array.
Here’s an example of running a query that incorporates json_each()
against this table:
SELECT DISTINCT
guest,
lunch
FROM
guests,
json_each(lunch)
WHERE json_each.value LIKE 'Apple Juice';
Result:
+-------+-------------------------------------------------+ | guest | lunch | +-------+-------------------------------------------------+ | Zohan | ["Beef Pie", "Fruit Salad", "Apple Juice"] | | Stacy | ["Chicken Curry", "Fruit Salad", "Apple Juice"] | | Aisha | ["Chicken Curry", "Apple Pie", "Apple Juice"] | +-------+-------------------------------------------------+
Here, we returned all guests who ordered apple juice with their lunch, along with their full lunch order.
If we want to return all guests who ordered apple “something”, we could do this:
SELECT DISTINCT
guest,
lunch
FROM
guests,
json_each(lunch)
WHERE json_each.value LIKE 'Apple%';
Result:
+-------+-------------------------------------------------+ | guest | lunch | +-------+-------------------------------------------------+ | Zohan | ["Beef Pie", "Fruit Salad", "Apple Juice"] | | Amy | ["Vegetable Quiche", "Apple", "Fruit Juice"] | | Stacy | ["Chicken Curry", "Fruit Salad", "Apple Juice"] | | Aisha | ["Chicken Curry", "Apple Pie", "Apple Juice"] | +-------+-------------------------------------------------+
Notice that I used the DISTINCT
clause in my query. This ensures we don’t get multiple rows returned for the same guest. To demonstrate what I mean, here’s the query again, but without the DISTINCT
clause:
SELECT
guest,
lunch
FROM
guests,
json_each(lunch)
WHERE json_each.value LIKE 'Apple%';
Result:
+-------+-------------------------------------------------+ | guest | lunch | +-------+-------------------------------------------------+ | Zohan | ["Beef Pie", "Fruit Salad", "Apple Juice"] | | Amy | ["Vegetable Quiche", "Apple", "Fruit Juice"] | | Stacy | ["Chicken Curry", "Fruit Salad", "Apple Juice"] | | Aisha | ["Chicken Curry", "Apple Pie", "Apple Juice"] | | Aisha | ["Chicken Curry", "Apple Pie", "Apple Juice"] | +-------+-------------------------------------------------+
This time Aisha appears twice. That’s because she ordered two apple dishes for lunch – Apple Pie and Apple Juice.