SQLite json_extract() escape characters on valid JSON object key

Question

I have valid JSON objects stored in a column called diff -- below is an example of a JSON object:

{
  "values_changed": {
    "root['item_status_code']": {
      "new_value": "(",
      "old_value": "-"
    }
  }
}

I'll admit that the name of the key root['item_status_code'] is a terrible choice and simpler keys should have been used -- it is still valid JSON however and I can't simply change the underlying data easily.

I should be able to write a query like this to extract the value of the key new_value in the JSON object, but I can't seem to get the escape characters to work as expected.

SELECT json_extract(diff, '$.values_changed.root['item_status_code'].new_value')
FROM items_diffs
WHERE json_extract(diff, '$.values_changed') IS NOT NULL;

obviously, this is not correct SQL syntax and will fail, but I can't seem to figure out the way to write this correctly.

Here's a fiddle with the data: https://www.db-fiddle.com/f/dN7qHjbeqxAxmfDHdDBDMU/2

Answer 1

I think I figure this out -- I was having trouble quoting this key, but I think I finally got it ...

--
json_extract(
    diff,
    '$.values_changed."root[''item_status_code'']".new_value'
) AS new_value