SQLite query with JSON functions returning NULL despite valid paths
I've been banging my head against this for hours... I'm prototyping a solution and I'm working on a project and hit a roadblock. I've searched everywhere and can't find a clear answer. I'm working with SQLite version 3.36.0 and trying to extract data from a JSON column using the built-in JSON functions. However, my query keeps returning NULL values even though the paths I believe are correct. I'm using the following table structure: ```sql CREATE TABLE data ( id INTEGER PRIMARY KEY, info JSON ); INSERT INTO data (info) VALUES ('{"name": "Alice", "details": {"age": 30, "city": "Wonderland"}}'), ('{"name": "Bob", "details": {"age": 25, "city": "Builderland"}}'); ``` Now, I want to select the age of each person, so I wrote this query: ```sql SELECT id, json_extract(info, '$.details.age') AS age FROM data; ``` However, this returns: ``` id | age ---|---- 1 | NULL 2 | NULL ``` I've confirmed that the JSON structure is correct and that the `info` column does contain the expected data. I even tried using `json_each` to troubleshoot, but still no luck. I also checked the SQLite documentation to ensure I am using the correct syntax for the JSON functions. I've tried variations like `json_extract(info, '$.details.age')` and `json_extract(info, '$.details')['age']`, but I keep hitting a wall with NULL results. Could there be an scenario with how the JSON data is stored, or is there something else I might be overlooking? I'm looking for any insights or troubleshooting steps to get this working. For context: I'm using Sql on macOS. How would you solve this? This is for a service running on Ubuntu 22.04.