# `EctoLibSql.JSON`
[🔗](https://github.com/ocean/ecto_libsql/blob/v0.9.1/lib/ecto_libsql/json.ex#L1)

Helper functions for working with JSON and JSONB data in SQLite.

libSQL 3.45.1 has comprehensive JSON1 extension built into the core with support for:
- JSON and JSONB (binary format) types
- Full suite of JSON functions: json_extract, json_type, json_array, json_object, json_each, json_tree
- MySQL/PostgreSQL compatible -> and ->> operators
- JSONB binary format for 5-10% smaller storage and faster processing

## JSON Functions

All JSON functions work with both text JSON and JSONB binary format. The functions
accept either format and automatically convert as needed.

### Core Functions

- `json_extract(json, path)` - Extract value at path
- `json_type(json, path)` - Get type of value at path (null, true, false, integer, real, text, array, object)
- `json_array(...args)` - Create JSON array from arguments
- `json_object(...pairs)` - Create JSON object from key-value pairs
- `json_each(json, path)` - Iterate over array/object members
- `json_tree(json, path)` - Recursively iterate over all values
- `json_valid(json)` - Check if JSON is valid
- `json(json)` - Convert text to canonical JSON representation
- `jsonb(json)` - Convert to binary JSONB format

### Operators

- `json -> 'path'` - Extract as JSON (always returns JSON or NULL)
- `json ->> 'path'` - Extract as text/SQL type (auto-converts)
- `json -> 'key'` - PostgreSQL-style shorthand for object keys
- `json -> 2` - PostgreSQL-style shorthand for array indices

## Usage with Ecto

JSON functions work naturally in Ecto queries via fragments:

    from u in User,
      where: json_extract(u.settings, "$.theme") == "dark",
      select: {u.id, u.settings -> "theme"}

Or use the helpers in this module:

    from u in User,
      where: fragment("json_extract(?, ?) = ?", u.settings, "$.theme", "dark"),
      select: {u.id, json_extract(u.settings, "$.theme")}

## JSONB Binary Format

JSONB is an efficient binary encoding of JSON with these benefits:
- 5-10% smaller file size than text JSON
- Faster processing (less than half the CPU cycles)
- Backwards compatible: all JSON functions accept both text and JSONB
- Transparent format conversion

Store as JSONB:
    {ok, _} = Repo.query("INSERT INTO users (data) VALUES (jsonb(?))", [json_string])

Retrieve and auto-convert:
    {:ok, result} = Repo.query("SELECT json(data) FROM users")

## Examples

    # Extract nested value
    {:ok, theme} = EctoLibSql.JSON.extract(state, data, "$.user.preferences.theme")

    # Create JSON object
    {:ok, obj} = EctoLibSql.JSON.object(state, ["name", "Alice", "age", 30])

    # Validate JSON
    {:ok, valid?} = EctoLibSql.JSON.is_valid(state, json_string)

    # Iterate over array elements
    {:ok, items} = EctoLibSql.JSON.each(state, array_json)

# `array`

```elixir
@spec array(EctoLibSql.State.t(), list()) :: {:ok, String.t()} | {:error, term()}
```

Create a JSON array from a list of values.

Each value will be inserted as-is, with strings becoming JSON text,
numbers becoming JSON numbers, nil becoming null, etc.

## Parameters

  - state: Connection state
  - values: List of values to include in the array

## Returns

  - `{:ok, json_array}` - JSON text representation of the array
  - `{:error, reason}` on failure

## Examples

    {:ok, array} = EctoLibSql.JSON.array(state, [1, 2.5, "hello", nil])
    # Returns: {:ok, "[1,2.5,"hello",null]"}

    # To nest JSON objects, pass them as json_object results
    {:ok, obj} = EctoLibSql.JSON.object(state, ["name", "Alice"])
    {:ok, array} = EctoLibSql.JSON.array(state, [obj, 42])

# `arrow_fragment`

```elixir
@spec arrow_fragment(String.t(), String.t() | integer(), :arrow | :double_arrow) ::
  String.t()
```

Helper to create SQL fragments for Ecto queries using JSON operators.

The -> and ->> operators are more concise in SQL than json_extract() calls.

## Parameters

  - json_column: Column name or fragment
  - path: JSON path (string or integer)
  - operator: `:arrow` for "->" (returns JSON) or `:double_arrow` for "->>" (returns SQL type)

## Returns

  - String for use in Ecto.Query.fragment/1

## Examples

    import Ecto.Query

    # Using arrow operator (returns JSON)
    from u in User,
      where: fragment(EctoLibSql.JSON.arrow_fragment("settings", "theme"), "!=", "null"),
      select: u

    # Using double-arrow operator (returns text/SQL type)
    from u in User,
      where: fragment(EctoLibSql.JSON.arrow_fragment("settings", "theme", :double_arrow), "=", "dark")

## Operators

- `->`  - Returns JSON value or NULL
- `->>` - Converts to SQL type (text, integer, real, or NULL)

Both operators support abbreviated syntax for object keys and array indices:
- `json -> 'key'` equivalent to `json_extract(json, '$.key')`
- `json -> 0` equivalent to `json_extract(json, '$[0]')`

# `convert`

```elixir
@spec convert(EctoLibSql.State.t(), String.t(), :json | :jsonb) ::
  {:ok, String.t() | binary()} | {:error, term()}
```

Convert text JSON to canonical form, optionally returning JSONB binary format.

Use `json()` to normalize and validate JSON text.
Use `jsonb()` to convert to binary format for more efficient storage/processing.

## Parameters

  - state: Connection state
  - json: JSON text string
  - format: `:json` for text format (default) or `:jsonb` for binary format

## Returns

  - `{:ok, json}` - Canonical JSON text (if format: :json)
  - `{:ok, jsonb}` - Binary JSONB blob (if format: :jsonb)
  - `{:error, reason}` on failure

## Examples

    # Normalize JSON text
    {:ok, canonical} = EctoLibSql.JSON.convert(state, ~s(  {"a":1}  ), :json)
    # Returns: {:ok, "{"a":1}"}

    # Convert to binary format
    {:ok, binary} = EctoLibSql.JSON.convert(state, ~s({"a":1}), :jsonb)
    # Returns: {:ok, <<binary_data>>}

## Benefits of JSONB

- 5-10% smaller file size
- Less than half the processing time
- Backwards compatible: all JSON functions accept JSONB
- Automatic format conversion between text and binary

# `depth`

```elixir
@spec depth(EctoLibSql.State.t(), String.t() | binary()) ::
  {:ok, pos_integer()} | {:error, term()}
```

Get the depth of a JSON structure.

Returns the maximum depth of nesting. Scalars have depth 1, empty arrays/objects have depth 1,
nested structures return greater depths.

## Parameters

  - state: Connection state
  - json: JSON text or JSONB binary data

## Returns

  - `{:ok, depth}` - Maximum nesting depth
  - `{:error, reason}` on failure

## Examples

    {:ok, depth} = EctoLibSql.JSON.depth(state, ~s(1))
    # Returns: {:ok, 1}

    {:ok, depth} = EctoLibSql.JSON.depth(state, ~s([1,2,3]))
    # Returns: {:ok, 2}

    {:ok, depth} = EctoLibSql.JSON.depth(state, ~s({"a":{"b":1}}))
    # Returns: {:ok, 3}

# `each`

```elixir
@spec each(EctoLibSql.State.t(), String.t() | binary(), String.t()) ::
  {:ok, [{String.t() | non_neg_integer(), term(), String.t()}]}
  | {:error, term()}
```

Iterate over elements of a JSON array or object members.

For arrays: Returns one row per array element with keys, values, and types.
For objects: Returns one row per key-value pair.

## Parameters

  - state: Connection state
  - json: JSON text or JSONB binary data
  - path: JSON path expression (optional, defaults to "$")

## Returns

  - `{:ok, [{key, value, type}]}` - List of members with metadata
  - `{:error, reason}` on failure

## Examples

    {:ok, items} = EctoLibSql.JSON.each(state, ~s([1,2,3]), "$")
    # Returns: {:ok, [{0, 1, "integer"}, {1, 2, "integer"}, {2, 3, "integer"}]}

    {:ok, items} = EctoLibSql.JSON.each(state, ~s({"a":1,"b":2}), "$")
    # Returns: {:ok, [{"a", 1, "integer"}, {"b", 2, "integer"}]}

## Notes

This function requires the virtual table extension (json_each).
Use in Ecto queries via fragments if the adapter doesn't support virtual tables.

# `extract`

```elixir
@spec extract(EctoLibSql.State.t(), String.t() | binary(), String.t()) ::
  {:ok, String.t() | integer() | float() | nil} | {:error, term()}
```

Extract a value from JSON at the specified path.

## Parameters

  - state: Connection state
  - json: JSON text or JSONB binary data
  - path: JSON path expression (e.g., "$.key" or "$[0]" or "$.nested.path")

## Returns

The return type depends on the extracted JSON value:
  - `{:ok, string}` - For JSON text values (e.g., "dark")
  - `{:ok, integer}` - For JSON integer values (e.g., 30)
  - `{:ok, float}` - For JSON real/float values (e.g., 99.99)
  - `{:ok, nil}` - For JSON null values or non-existent paths
  - `{:ok, json_text}` - For JSON objects/arrays, returned as JSON text string
  - `{:error, reason}` - On query failure

## Examples

    {:ok, theme} = EctoLibSql.JSON.extract(state, ~s({"theme":"dark"}), "$.theme")
    # Returns: {:ok, "dark"}

    {:ok, age} = EctoLibSql.JSON.extract(state, ~s({"user":{"age":30}}), "$.user.age")
    # Returns: {:ok, 30}

    {:ok, items} = EctoLibSql.JSON.extract(state, ~s({"items":[1,2,3]}), "$.items")
    # Returns: {:ok, "[1,2,3]"}  (JSON array as text)

    {:ok, nil} = EctoLibSql.JSON.extract(state, ~s({"a":1}), "$.missing")
    # Returns: {:ok, nil}  (path doesn't exist)

## Notes

- JSON objects and arrays are returned as JSON text strings
- Use `-> operator in SQL queries to preserve JSON structure, or `->> operator to convert to SQL types
- Works with both text JSON and JSONB binary format (format conversion is automatic)
- For nested JSON structures, you can chain extractions or use JSON paths like "$.user.address.city"

# `insert`

```elixir
@spec insert(EctoLibSql.State.t(), String.t() | binary(), String.t(), term()) ::
  {:ok, String.t()} | {:error, term()}
```

Insert a value into JSON at a specific path.

Adds a value without replacing existing content. For arrays, inserts before the specified index.

## Parameters

  - state: Connection state
  - json: JSON text or JSONB binary data
  - path: JSON path where to insert
  - value: Value to insert

## Returns

  - `{:ok, modified_json}` - JSON with inserted value
  - `{:error, reason}` on failure

## Examples

    {:ok, json} = EctoLibSql.JSON.insert(state, ~s([1,3,4]), "$[1]", 2)
    # Returns: {:ok, "[1,2,3,4]"}

    {:ok, json} = EctoLibSql.JSON.insert(state, ~s({"a":1}), "$.b", 2)
    # Returns: {:ok, "{"a":1,"b":2}"}

# `is_valid`

```elixir
@spec is_valid(EctoLibSql.State.t(), String.t()) ::
  {:ok, boolean()} | {:error, term()}
```

Check if a string is valid JSON.

## Parameters

  - state: Connection state
  - json: String to validate as JSON

## Returns

  - `{:ok, true}` if valid JSON
  - `{:ok, false}` if not valid JSON
  - `{:error, reason}` on failure

## Examples

    {:ok, true} = EctoLibSql.JSON.is_valid(state, ~s({"valid":true}))
    {:ok, false} = EctoLibSql.JSON.is_valid(state, "not json")

# `json_length`

```elixir
@spec json_length(EctoLibSql.State.t(), String.t() | binary(), String.t()) ::
  {:ok, non_neg_integer() | nil} | {:error, term()}
```

Get the length of a JSON array or number of keys in JSON object.

Named `json_length/2,3` to avoid shadowing Elixir's `Kernel.length/1`.

## Parameters

  - state: Connection state
  - json: JSON text or JSONB binary data
  - path: JSON path expression (optional, defaults to "$")

## Returns

  - `{:ok, length}` - Number of elements/keys
  - `{:ok, nil}` - For non-array/object values
  - `{:error, reason}` on failure

## Examples

    {:ok, len} = EctoLibSql.JSON.json_length(state, ~s([1,2,3]))
    # Returns: {:ok, 3}

    {:ok, len} = EctoLibSql.JSON.json_length(state, ~s({"a":1,"b":2}))
    # Returns: {:ok, 2}

# `json_quote`

```elixir
@spec json_quote(EctoLibSql.State.t(), term()) :: {:ok, String.t()} | {:error, term()}
```

Quote a value for use in JSON.

Converts SQL values to properly escaped JSON string representation.
Useful for building JSON values dynamically.

Named `json_quote/2` to avoid shadowing Elixir's Kernel.quote/2 macro.

## Parameters

  - state: Connection state
  - value: Value to quote (string, number, nil, etc.)

## Returns

  - `{:ok, json_string}` - Properly quoted JSON string
  - `{:error, reason}` on failure

## Examples

    {:ok, quoted} = EctoLibSql.JSON.json_quote(state, "hello "world"")
    # Returns: {:ok, ""hello \"world\"""}

    {:ok, quoted} = EctoLibSql.JSON.json_quote(state, "test")
    # Returns: {:ok, ""test""}

# `keys`

```elixir
@spec keys(EctoLibSql.State.t(), String.t() | binary(), String.t()) ::
  {:ok, String.t() | nil} | {:error, term()}
```

Get all keys from a JSON object.

Returns NULL if the JSON is not an object.

## Parameters

  - state: Connection state
  - json: JSON text or JSONB binary data
  - path: JSON path expression (optional, defaults to "$")

## Returns

  - `{:ok, keys}` - JSON array of keys
  - `{:ok, nil}` - If not an object
  - `{:error, reason}` on failure

## Examples

    {:ok, keys} = EctoLibSql.JSON.keys(state, ~s({"name":"Alice","age":30}))
    # Returns: {:ok, "["age","name"]"}  (sorted)

# `object`

```elixir
@spec object(EctoLibSql.State.t(), list()) :: {:ok, String.t()} | {:error, term()}
```

Create a JSON object from a list of key-value pairs.

Arguments must alternate between string keys and values. Values can be
of any type (strings, numbers, nil/null, nested objects/arrays, etc.).

## Parameters

  - state: Connection state
  - pairs: List of alternating [key1, value1, key2, value2, ...]

## Returns

  - `{:ok, json_object}` - JSON text representation of the object
  - `{:error, reason}` on failure

## Examples

    {:ok, obj} = EctoLibSql.JSON.object(state, ["name", "Alice", "age", 30])
    # Returns: {:ok, "{"name":"Alice","age":30}"}

    # Keys must be strings, values can be any type
    {:ok, obj} = EctoLibSql.JSON.object(state, [
      "id", 1,
      "active", true,
      "balance", 99.99,
      "tags", nil
    ])

## Errors

Returns `{:error, reason}` if:
- Number of arguments is not even
- Any key is not a string

# `patch`

```elixir
@spec patch(EctoLibSql.State.t(), String.t() | binary(), String.t() | binary()) ::
  {:ok, String.t()} | {:error, term()}
```

Apply a JSON Merge Patch to modify JSON (RFC 7396).

Implements RFC 7396 JSON Merge Patch semantics. The patch is a JSON object where:
- **Top-level keys** are object keys in the target, not JSON paths
- **Values** replace the corresponding object values in the target
- **Nested objects** are merged recursively
- **null values** remove the key from the target object

To update nested structures, the patch object must reflect the nesting level.

## Parameters

  - state: Connection state
  - json: JSON text or JSONB binary data (must be an object)
  - patch: JSON object with merge patch semantics (keys are object keys, not paths)

## Returns

  - `{:ok, modified_json}` - JSON after applying merge patch
  - `{:error, reason}` on failure

## Examples

    # Top-level key replacement
    {:ok, json} = EctoLibSql.JSON.patch(state, ~s({"a":1,"b":2}), ~s({"a":10}))
    # Returns: {:ok, "{"a":10,"b":2}"}

    # Add new top-level key
    {:ok, json} = EctoLibSql.JSON.patch(state, ~s({"a":1,"b":2}), ~s({"c":3}))
    # Returns: {:ok, "{"a":1,"b":2,"c":3}"}

    # Remove key with null
    {:ok, json} = EctoLibSql.JSON.patch(state, ~s({"a":1,"b":2,"c":3}), ~s({"b":null}))
    # Returns: {:ok, "{"a":1,"c":3}"}

    # Nested object merge (replaces entire nested object)
    {:ok, json} = EctoLibSql.JSON.patch(state, ~s({"user":{"name":"Alice","age":30}}), ~s({"user":{"age":31}}))
    # Returns: {:ok, "{"user":{"age":31}}"}  (replaces entire user object, not a deep merge)

## Notes

- This implements RFC 7396 JSON Merge Patch, NOT RFC 6902 JSON Patch
- Object keys in the patch are literal keys, not JSON paths (use "a" not "$.a")
- For nested structures, the patch replaces the entire value at that key (not a deep recursive merge)
- To perform deep merges or path-based updates, use `json_set/4` or `json_replace/4` instead
- Works with both text JSON and JSONB binary format

# `remove`

```elixir
@spec remove(EctoLibSql.State.t(), String.t() | binary(), String.t() | [String.t()]) ::
  {:ok, String.t()} | {:error, term()}
```

Remove one or more elements from JSON.

## Parameters

  - state: Connection state
  - json: JSON text or JSONB binary data
  - paths: Single path string or list of path strings to remove

## Returns

  - `{:ok, modified_json}` - JSON with specified paths removed
  - `{:error, reason}` on failure

## Examples

    {:ok, json} = EctoLibSql.JSON.remove(state, ~s({"a":1,"b":2,"c":3}), "$.b")
    # Returns: {:ok, "{"a":1,"c":3}"}

    {:ok, json} = EctoLibSql.JSON.remove(state, ~s([1,2,3,4,5]), ["$[0]", "$[2]"])
    # Returns: {:ok, "[2,3,5]"}
    # Note: Paths are removed in order; after removing $[0], the original $[2] is now at $[1]

# `replace`

```elixir
@spec replace(EctoLibSql.State.t(), String.t() | binary(), String.t(), term()) ::
  {:ok, String.t()} | {:error, term()}
```

Replace a value in JSON at a specific path (if it exists).

Unlike `set/4`, replace only modifies existing paths. Non-existent paths are ignored.

## Parameters

  - state: Connection state
  - json: JSON text or JSONB binary data
  - path: JSON path to replace
  - value: New value

## Returns

  - `{:ok, modified_json}` - JSON with replaced value
  - `{:error, reason}` on failure

## Examples

    {:ok, json} = EctoLibSql.JSON.replace(state, ~s({"a":1,"b":2}), "$.a", 10)
    # Returns: {:ok, "{"a":10,"b":2}"}

    # Non-existent path is ignored
    {:ok, json} = EctoLibSql.JSON.replace(state, ~s({"a":1}), "$.z", 99)
    # Returns: {:ok, "{"a":1}"}

# `set`

```elixir
@spec set(EctoLibSql.State.t(), String.t() | binary(), String.t(), term()) ::
  {:ok, String.t()} | {:error, term()}
```

Set a value in JSON at a specific path.

If the path does not exist, it is created. If the path exists, it is replaced.

## Parameters

  - state: Connection state
  - json: JSON text or JSONB binary data
  - path: JSON path where to set the value
  - value: Value to set at the path

## Returns

  - `{:ok, modified_json}` - JSON with updated value
  - `{:error, reason}` on failure

## Examples

    {:ok, json} = EctoLibSql.JSON.set(state, ~s({"a":1}), "$.b", 2)
    # Returns: {:ok, "{"a":1,"b":2}"}

    {:ok, json} = EctoLibSql.JSON.set(state, ~s({"user":"Alice"}), "$.active", true)
    # Returns: {:ok, "{"user":"Alice","active":true}"}

# `tree`

```elixir
@spec tree(EctoLibSql.State.t(), String.t() | binary(), String.t()) ::
  {:ok, [{String.t(), term(), String.t()}]} | {:error, term()}
```

Recursively iterate over all values in a JSON structure.

Returns all values at all levels of nesting with their paths and types.
Useful for flattening JSON or finding all values matching criteria.

## Parameters

  - state: Connection state
  - json: JSON text or JSONB binary data
  - path: JSON path expression (optional, defaults to "$")

## Returns

  - `{:ok, [{full_key, atom, type}]}` - List of all values with paths
  - `{:error, reason}` on failure

## Examples

    {:ok, tree} = EctoLibSql.JSON.tree(state, ~s({"a":{"b":1},"c":[2,3]}), "$")
    # Returns complete tree of all values with their full paths

## Notes

This function requires the virtual table extension (json_tree).
Returns more detailed information than json_each (includes all nested values).

# `type`

```elixir
@spec type(EctoLibSql.State.t(), String.t() | binary(), String.t()) ::
  {:ok, String.t()} | {:error, term()}
```

Get the type of a value in JSON at the specified path.

## Parameters

  - state: Connection state
  - json: JSON text or JSONB binary data
  - path: JSON path expression (optional, defaults to "$" for root)

## Returns

  - `{:ok, type}` - One of: null, true, false, integer, real, text, array, object
  - `{:error, reason}` on failure

## Examples

    {:ok, type} = EctoLibSql.JSON.type(state, ~s([1,2,3]), "$")
    # Returns: {:ok, "array"}

    {:ok, type} = EctoLibSql.JSON.type(state, ~s({"name":"Alice"}), "$.name")
    # Returns: {:ok, "text"}

---

*Consult [api-reference.md](api-reference.md) for complete listing*