Function jsonPath

Use this to access nested properties in a JSON column. You can also use the dot notation with Attribute, but this works with any values, not just attributes.

Example

sql`${jsonPath('data', ['name'])} = '"John"'`
Copy

will produce

-- postgres
"data"->'name' = '"John"'
-- sqlite, mysql, mariadb
JSON_EXTRACT("data", '$.name') = '"John"'
Copy

Example

// notice here that 0 is a number, not a string. It will be treated as an array index.
sql`${jsonPath('array', [0])}`
Copy

will produce

-- postgres
"array"->0
-- sqlite, mysql, mariadb
JSON_EXTRACT(`array`, '$[0]')
Copy

Example

// notice here that 0 is a string, not a number. It will be treated as an object key.
sql`${jsonPath('object', ['0'])}`
Copy

will produce

-- postgres
"object"->'0'
-- sqlite, mysql, mariadb
JSON_EXTRACT(`object`, '$.0')
Copy