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