Escaping weird path names with sqlite json_extract

Question

I'm trying to figure out how to escape weird field values in a json_extract expression. Here's my table:

create table records (
  record_pk text primary key,
  config json not null default '{}',

  check (
    json_valid(config) and
    json_type(config) = 'object'
  )
);

Note that the config field is always a JSON hash. Here's the value for config in one of the records:

{
  "Joe O'Toole": {
    "said \"whatever\" dude": {
      "val u": true
    }
  }
}

Note that I purposefully used weird field names to test for edge cases. I can't figure out how to query for that record. The query must exactly query for this path:

Joe O'Toole
   said "whatever" dude
      val u
        true

Here's a query that doesn't work:

select *
from records
where
  json_extract(
    config,
    '$."Joe O''Toole"."said ""whatever"" dude"."val u"'
  ) is not null

How should that query be modified to find the record?

Here's my system information:

---- Ubuntu
No LSB modules are available.
Distributor ID: Ubuntu
Description:    Ubuntu 22.04.5 LTS
Release:        22.04
Codename:       jammy
---- sqlite3
3.37.2 2022-01-06 13:25:41 872ba256cbf61d9290b571c0e6d82a20c224ca3ad82971edc46b29818d5dalt1

Shawn · Accepted Answer · 2025-10-01 07:05:58Z

0

The JSON path parser accepts backslashes to escape double quotes, much like JSON itself:

sqlite> SELECT config -> '$."Joe O''Toole"."said \"whatever\" dude"."val u"' FROM records;
config -> '$."Joe O''Toole"."said \"whatever\" dude"."val u"
------------------------------------------------------------
true

(And, as you're already doing, you have to double up the single quote to escape it for the top-level SQL string)

answered Oct 1 at 7:05

Shawn

53.9k3 gold badges29 silver badges74 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Escaping weird path names with sqlite json_extract

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related