jsonsqlitesqlite-json1

How to retrieve a value in a json object in sqlite when the key is an empty string?

I am processing an sqlite table which contains json objects. These json objects have keys that are empty strings. How can I retrieve the value? For example:

select json_extract('{"foo": "bar", "":"empty"}', '$.foo') as data;
-result: "bar"

How can I retrieve "empty"?

Solution

  • Using your example:

    sqlite> SELECT value FROM json_each('{"foo":"bar","":"empty"}') WHERE key = '';
value     
----------
empty

    As part of a larger query from a table:

    SELECT (SELECT j.value FROM json_each(t.your_json_column) AS j WHERE j.key = '') AS data
FROM your_table AS t;