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

Question

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"?

Answer 1

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;