Variable jsonPath

Type declaration

• • (expression, path): 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.

    Parameters

    • expression: unknown

      The expression to access the property on.

    • path: readonly (string | number)[]

      The path to the property. If a number is used, it will be treated as an array index, otherwise as a key.

    Returns JsonPath

    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