Use a JSON predicate in Sqlite query to get an array item

Question

I am trying to retrieve a single item from a JSON array in Sqlite using a predicate.

This is my sample data set:

{
    "book": [
        {
            "author": "Nigel Rees",
            "category": "reference",
            "price": 8.95,
            "title": "Sayings of the Century"
        },
        {
            "author": "Herman Melville",
            "category": "fiction",
            "isbn": "0-553-21311-3",
            "price": 8.99,
            "title": "Moby Dick"
        },
        {
            "author": "J.R.R. Tolkien",
            "category": "fiction",
            "isbn": "0-395-19395-8",
            "price": 22.99,
            "title": "The Lord of the Rings"
        }
    ]
}

I would like to retrieve only the item where the price is 22.99.

In XPath I would do something like this: //book[@price='22.99']

I tried some JSON path evaluators online (https://jsonpath.com/) and this should be working: $.book[?(@.price == 22.99)]

However, when I try to use the same path in a SELECT query in Sqlite, it throws the following exception:

Result: JSON path error near '[?(@.price == 22.99)]'

Am I missing something? When I try paths without a predicate (like $.book[2].title) it works.

My full SQL query is (the JSON is in the content field):

SELECT Json_extract(content, '$.book[?(@.price == 22.99)]')
FROM json_test
WHERE id = 1

PS: I know I could use the WHERE statement for the query, but I would prefer to get it working using JSON path only.

See sqlite.org/json1.html#path_arguments for the syntax supported by json1 paths. — Shawn
– Shawn, Commented Feb 4, 2021 at 11:20
@Shawn Hmm, so do I understand it correctly that predicates are not supported? Only elements and indices? Shame. But thanks for the answer. — Marek Stejskal
– Marek Stejskal, Commented Feb 4, 2021 at 11:24

Shawn · Accepted Answer · 2021-02-04 11:28:20Z

1

Something along the lines of:

SELECT j.value
FROM json_test AS jt
JOIN json_each(jt.content, '$.book') AS j
WHERE jt.id = 1 AND json_extract(j.value, '$.price') = 22.99

You can't avoid using a WHERE if you want to filter on a value in a json object.

answered Feb 4, 2021 at 11:28

Shawn

53.9k3 gold badges29 silver badges74 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Use a JSON predicate in Sqlite query to get an array item

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related