> ## Documentation Index
> Fetch the complete documentation index at: https://docs.powersync.com/llms.txt
> Use this file to discover all available pages before exploring further.

# JSON, Arrays and Custom Types

> Sync JSON, JSONB, arrays, and other custom Postgres types through PowerSync by serializing them to text.

PowerSync supports JSON/JSONB and array columns. They are synced as JSON text and can be queried with SQLite JSON functions on the client. Other custom Postgres types can be synced by serializing their values to text in the client-side schema. When updating client data, you have the option to replace the entire column value with a string or enable [advanced schema options](#advanced-schema-options-to-process-writes) to track more granular changes and include custom metadata.

## JSON and JSONB

The PowerSync Service treats JSON and JSONB columns as text and provides many helpers for working with JSON in [Sync Streams](/sync/streams/overview) (or legacy [Sync Rules](/sync/rules/overview)).

**Note:** Native Postgres arrays, JSON arrays, and JSONB arrays are effectively all equivalent in PowerSync.

### Postgres

JSON columns are represented as:

```sql theme={null}
ALTER TABLE todos
ADD COLUMN custom_payload json;
```

### Sync Streams

<Tabs>
  <Tab title="Sync Streams">
    PowerSync treats JSON columns as text. Use `json_extract()` and other JSON functions in stream queries. Subscribe per list to sync only that list's todos:

    ```yaml theme={null}
    config:
      edition: 3
    streams:
      my_json_todos:
        auto_subscribe: true
        with:
          owned_lists: SELECT id AS list_id FROM lists WHERE owner_id = auth.user_id()
        query: SELECT * FROM todos WHERE json_extract(custom_payload, '$.json_list') IN owned_lists
    ```

    The client subscribes once per list (e.g. `db.syncStream('my_json_todos', { list_id: listId }).subscribe()`).
  </Tab>

  <Tab title="Sync Rules (Legacy)">
    PowerSync treats JSON columns as text and provides transformation functions in Sync Rules such as `json_extract()`.

    ```yaml theme={null}
    bucket_definitions:
      my_json_todos:
        # Separate bucket per To-Do list
        parameters: SELECT id AS list_id FROM lists WHERE owner_id = request.user_id()
        data:
          - SELECT * FROM todos WHERE json_extract(custom_payload, '$.json_list') = bucket.list_id
    ```
  </Tab>
</Tabs>

### Client SDK

**Schema**

Add your JSON column as a `text` column in your client-side schema definition. For advanced update tracking, see [Advanced Schema Options](#advanced-schema-options).

<Tabs>
  <Tab title="Dart">
    ```dart theme={null}
    Table(
      name: 'todos',
      columns: [
        Column.text('custom_payload'),
        // ... other columns ...
      ],
      // Optionally, enable advanced update tracking options (see details at the end of this page):
      trackPreviousValues: true, 
      trackMetadata: true, 
      ignoreEmptyUpdates: true, 
    )
    ```
  </Tab>

  <Tab title="JavaScript">
    ```javascript theme={null}
    const todos = new Table(
      {
        custom_payload: column.text,
        // ... other columns ...
      },
      {
        // Optionally, enable advanced update tracking options (see details at the end of this page):
        trackPrevious: true,
        trackMetadata: true,
        ignoreEmptyUpdates: true,
      }
    );
    ```
  </Tab>

  <Tab title=".NET">
    ```csharp theme={null}
    new Table
    {
      Name = "todos",
      Columns =
      {
        ["custom_payload"] = ColumnType.Text,
        // ... other columns ...
      },
      // Optionally, enable advanced update tracking options (see details at the end of this page):
      TrackPreviousValues = new TrackPreviousOptions(),
      TrackMetadata = true,
      IgnoreEmptyUpdates = true
    }
    ```
  </Tab>

  <Tab title="Rust">
    Example not yet available.
  </Tab>
</Tabs>

**Writing Changes**

You can write the entire updated column value as a string, or, with `trackPreviousValues` enabled, compare the previous and new values to process only the changes you care about:

<Tabs>
  <Tab title="Dart">
    ```dart theme={null}
    // Full replacement (basic):
    await db.execute('UPDATE todos set custom_payload = ?, _metadata = ? WHERE id = ?', [
      '{"foo": "bar", "baz": 123}',
      'op-metadata-example', // Example metadata value
      '00000000-0000-0000-0000-000000000000'
    ]);

    // Diffing columns in uploadData (advanced):
    // See details about these advanced schema options at the end of this page
    import 'dart:convert';

    if (op.op == UpdateType.put && op.previousValues != null) {
      var oldJson = jsonDecode(op.previousValues['custom_payload'] ?? '{}');
      var newJson = jsonDecode(op.opData['custom_payload'] ?? '{}');
      var metadata = op.metadata; // Access metadata here
      // Compare oldJson and newJson to determine what changed
      // Use metadata as needed as you process the upload
    }
    ```
  </Tab>

  <Tab title="JavaScript">
    ```javascript theme={null}
    // Full replacement (basic):
    await db.execute(
      'UPDATE todos set custom_payload = ?, _metadata = ? WHERE id = ?',
      ['{"foo": "bar", "baz": 123}', 'op-metadata-example', '00000000-0000-0000-0000-000000000000']
    );

    // Diffing columns in uploadData (advanced):
    // See details about these advanced schema options at the end of this page
    if (op.op === UpdateType.PUT && op.previousValues) {
      const oldJson = JSON.parse(op.previousValues['custom_payload'] ?? '{}');
      const newJson = JSON.parse(op.opData['custom_payload'] ?? '{}');
      const metadata = op.metadata; // Access metadata here
      // Compare oldJson and newJson to determine what changed
      // Use metadata as needed as you process the upload
    }
    ```
  </Tab>

  <Tab title=".NET">
    ```csharp theme={null}
    // Full replacement (basic):
    await db.Execute(
      "UPDATE todos SET custom_payload = ?, _metadata = ? WHERE id = ?",
      new object[] { "{\"foo\": \"bar\", \"baz\": 123}", "op-metadata-example", "00000000-0000-0000-0000-000000000000" }
    );

    // Diffing columns in UploadData (advanced):
    // See details about these advanced schema options at the end of this page
    using Newtonsoft.Json;

    if (op.Op.ToString() == "PUT" && op.PreviousValues != null)
    {
      var oldJson = JsonConvert.DeserializeObject<Dictionary<string, object>>(
        op.PreviousValues.GetValueOrDefault("custom_payload", "{}")?.ToString() ?? "{}"
      );
      var newJson = JsonConvert.DeserializeObject<Dictionary<string, object>>(
        (op.OpData != null ? op.OpData.GetValueOrDefault("custom_payload", "{}")?.ToString() ?? "{}" : "{}") ?? "{}"
      );
      var metadata = op.Metadata; // Access metadata here
      // Compare oldJson and newJson to determine what changed
      // Use metadata as needed as you process the upload
    }
    ```
  </Tab>

  <Tab title="Rust">
    Example not yet available.
  </Tab>
</Tabs>

## Arrays

PowerSync treats array columns as JSON text. This means that the SQLite JSON operators can be used on any array columns.

Additionally, array membership is supported in [Sync Streams](/sync/streams/overview) (or legacy [Sync Rules](/sync/rules/overview)) so you can sync rows based on whether a parameter value appears in an array column.

**Note:** Native Postgres arrays, JSON arrays, and JSONB arrays are effectively all equivalent in PowerSync.

### Postgres

Array columns are defined in Postgres using the following syntax:

```sql theme={null}
ALTER TABLE todos
ADD COLUMN unique_identifiers text[];
```

### Sync Streams

Array columns are converted to text by the PowerSync Service. A text array as defined above would be synced to clients as the following string:

`["00000000-0000-0000-0000-000000000000", "12345678-1234-1234-1234-123456789012"]`

**Array Membership**

<Tabs>
  <Tab title="Sync Streams">
    Sync rows where a subscription parameter value is in the row's array column using `IN`:

    ```yaml theme={null}
    config:
      edition: 3
    streams:
      custom_todos:
        query: SELECT * FROM todos WHERE subscription.parameter('list_id') IN unique_identifiers
    ```

    The client subscribes per list (e.g. `db.syncStream('custom_todos', { list_id: listId }).subscribe()`).
  </Tab>

  <Tab title="Sync Rules (Legacy)">
    It's possible to sync rows dynamically based on the contents of array columns using the `IN` operator:

    ```yaml theme={null}
    bucket_definitions:
      custom_todos:
        # Separate bucket per To-Do list
        parameters: SELECT id AS list_id FROM lists WHERE owner_id = request.user_id()
        data:
          - SELECT * FROM todos WHERE bucket.list_id IN unique_identifiers
    ```
  </Tab>
</Tabs>

<Tip>
  See these additional details when using the `IN` operator: [Operators](/sync/supported-sql#operators)
</Tip>

### Client SDK

**Schema**

Add your array column as a `text` column in your client-side schema definition. For advanced update tracking, see [Advanced Schema Options](#advanced-schema-options).

<Tabs>
  <Tab title="JavaScript">
    ```javascript theme={null}
    const todos = new Table(
      {
        unique_identifiers: column.text,
        // ... other columns ...
      },
      {
        // Optionally, enable advanced update tracking options (see details at the end of this page):
        trackPrevious: true,
        trackMetadata: true,
        ignoreEmptyUpdates: true,
      }
    );
    ```
  </Tab>

  <Tab title="Dart">
    ```dart theme={null}
    Table(
      name: 'todos',
      columns: [
        Column.text('unique_identifiers'),
        // ... other columns ...
      ],

      // Optionally, enable advanced update tracking options (see details at the end of this page):
      trackPreviousValues: true, 
      trackMetadata: true, 
      ignoreEmptyUpdates: true, 
    )
    ```
  </Tab>

  <Tab title=".NET">
    ```csharp theme={null}
    new Table
    {
      Name = "todos",
      Columns =
      {
        ["unique_identifiers"] = ColumnType.Text,
        // ... other columns ...
      },
      // Optionally, enable advanced update tracking options (see details at the end of this page):
      TrackPreviousValues = new TrackPreviousOptions(),
      TrackMetadata = true,
      IgnoreEmptyUpdates = true
    }
    ```
  </Tab>

  <Tab title="Rust">
    Example not yet available.
  </Tab>
</Tabs>

**Writing Changes**

You can write the entire updated column value as a string, or, with `trackPreviousValues` enabled, compare the previous and new values to process only the changes you care about:

<Tabs>
  <Tab title="JavaScript">
    ```javascript theme={null}
    // Full replacement (basic):
    await db.execute(
      'UPDATE todos set unique_identifiers = ?, _metadata = ? WHERE id = ?',
      ['["DEADBEEF-DEAD-BEEF-DEAD-BEEFDEADBEEF", "ABCDEFAB-ABCD-ABCD-ABCD-ABCDEFABCDEF"]', 'op-metadata-example', '00000000-0000-0000-0000-000000000000']
    );

    // Diffing columns in uploadData (advanced):
    // See details about these advanced schema options at the end of this page
    if (op.op === UpdateType.PUT && op.previousValues) {
      const oldArray = JSON.parse(op.previousValues['unique_identifiers'] ?? '[]');
      const newArray = JSON.parse(op.opData['unique_identifiers'] ?? '[]');
      const metadata = op.metadata; // Access metadata here
      // Compare oldArray and newArray to determine what changed
      // Use metadata as needed as you process the upload
    }
    ```
  </Tab>

  <Tab title="Dart">
    ```dart theme={null}
    // Full replacement (basic):
    await db.execute('UPDATE todos set unique_identifiers = ?, _metadata = ? WHERE id = ?', [
      '["DEADBEEF-DEAD-BEEF-DEAD-BEEFDEADBEEF", "ABCDEFAB-ABCD-ABCD-ABCD-ABCDEFABCDEF"]',
      'op-metadata-example', // Example metadata value
      '00000000-0000-0000-0000-000000000000'
    ]);

    // Diffing columns in uploadData (advanced):
    // See details about these advanced schema options at the end of this page
    if (op.op == UpdateType put && op.previousValues != null) {
      final oldArray = jsonDecode(op.previousValues['unique_identifiers'] ?? '[]');
      final newArray = jsonDecode(op.opData['unique_identifiers'] ?? '[]');
      final metadata = op.metadata; // Access metadata here
      // Compare oldArray and newArray to determine what changed
      // Use metadata as needed as you process the upload
    }
    ```
  </Tab>

  <Tab title=".NET">
    ```csharp theme={null}
    // Full replacement (basic):
    await db.Execute(
      "UPDATE todos SET unique_identifiers = ?, _metadata = ? WHERE id = ?",
      new object[] { 
        "[\"DEADBEEF-DEAD-BEEF-DEAD-BEEFDEADBEEF\", \"ABCDEFAB-ABCD-ABCD-ABCD-ABCDEFABCDEF\"]", 
        "op-metadata-example", 
        "00000000-0000-0000-0000-000000000000" 
      }
    );

    // Diffing columns in UploadData (advanced):
    // See details about these advanced schema options at the end of this page
    using Newtonsoft.Json;

    if (op.Op.ToString() == "PUT" && op.PreviousValues != null)
    {
      var oldArray = JsonConvert.DeserializeObject<List<string>>(
        (op.PreviousValues != null ? op.PreviousValues.GetValueOrDefault("unique_identifiers", "[]")?.ToString() : "[]") ?? "[]"
      );
      var newArray = JsonConvert.DeserializeObject<List<string>>(
        (op.OpData != null ? op.OpData.GetValueOrDefault("unique_identifiers", "[]")?.ToString() : "[]") ?? "[]"
      );
      var metadata = op.Metadata; // Access metadata here
      // Compare oldArray and newArray to determine what changed
      // Use metadata as needed as you process the upload
    }
    ```
  </Tab>

  <Tab title="Rust">
    Example not yet available.
  </Tab>
</Tabs>

<Note>
  **Attention Supabase users:** Supabase can handle writes with arrays, but you must convert from string to array using `jsonDecode` in the connector's `uploadData` function. The default implementation of `uploadData` does not handle complex types like arrays automatically.
</Note>

## Custom Types

PowerSync respects Postgres custom types: DOMAIN types sync as their inner type, custom type columns as JSON objects, arrays of custom types as JSON arrays, and ranges (and multi-ranges) as structured JSON. This behavior is the default for Sync Streams. For configuration and legacy behavior, see [Compatibility](/sync/advanced/compatibility#custom-postgres-types). For type handling in queries, see [Types](/sync/types).

### Postgres

Postgres allows developers to create custom data types for columns. For example:

```sql theme={null}
create type location_address AS (
    street text,
    city text,
    state text,
    zip numeric
);
```

### Sync Streams

<Tabs>
  <Tab title="Sync Streams">
    The custom type column is serialized as JSON and you can use `json_extract()` and other JSON functions in stream queries:

    ```yaml theme={null}
    config:
      edition: 3
    streams:
      todos_by_city:
        query: SELECT * FROM todos WHERE json_extract(location, '$.city') = subscription.parameter('city')
    ```
  </Tab>

  <Tab title="Sync Rules (Legacy)">
    Custom type columns are converted to text by the PowerSync Service.
    Depending on whether the `custom_postgres_types` [compatibility option](/sync/advanced/compatibility) is enabled,
    PowerSync would sync the row as:

    * `{"street":"1000 S Colorado Blvd.","city":"Denver","state":"CO","zip":80211}` if the option is enabled.
    * `("1000 S Colorado Blvd.",Denver,CO,80211)` if the option is disabled.

    You can use regular string and JSON manipulation functions in Sync Rules. This means that individual values of the type
    can be synced with `json_extract` if the `custom_postgres_types` compatibility option is enabled.
    Without the option, the entire column must be synced as text.
  </Tab>
</Tabs>

### Client SDK

**Schema**

Add your custom type column as a `text` column in your client-side schema definition. For advanced update tracking, see [Advanced Schema Options](#advanced-schema-options).

<Tabs>
  <Tab title="JavaScript">
    ```javascript theme={null}
    const todos = new Table(
      {
        location: column.text,
        // ... other columns ...
      },
      {
        // Optionally, enable advanced update tracking options (see details at the end of this page):
        trackPrevious: true,
        trackMetadata: true,
        ignoreEmptyUpdates: true,
      }
    );
    ```
  </Tab>

  <Tab title="Dart">
    ```dart theme={null}
    Table(
      name: 'todos',
      columns: [
        Column.text('location'),
        // ... other columns ...
      ],

      // Optionally, enable advanced update tracking options (see details at the end of this page):
      trackPreviousValues: true, 
      trackMetadata: true, 
      ignoreEmptyUpdates: true, 
    )
    ```
  </Tab>

  <Tab title=".NET">
    ```csharp theme={null}
    new Table
    {
      Name = "todos",
      Columns =
      {
        ["location"] = ColumnType.Text,
        // ... other columns ...
      },
      // Optionally, enable advanced update tracking options (see details at the end of this page):
      TrackPreviousValues = new TrackPreviousOptions(),
      TrackMetadata = true,
      IgnoreEmptyUpdates = true
    }
    ```
  </Tab>

  <Tab title="Rust">
    Example not yet available.
  </Tab>
</Tabs>

**Writing Changes**

You can write the entire updated column value as a string, or, with `trackPreviousValues` enabled, compare the previous and new values to process only the changes you care about:

<Tabs>
  <Tab title="JavaScript">
    ```javascript theme={null}
    // Full replacement (basic):
    await db.execute(
      'UPDATE todos set location = ?, _metadata = ? WHERE id = ?',
      ['("1234 Update Street",Denver,CO,80212)', 'op-metadata-example', 'faffcf7a-75f9-40b9-8c5d-67097c6b1c3b']
    );

    // Diffing columns in uploadData (advanced):
    // See details about these advanced schema options at the end of this page
    if (op.op === UpdateType.PUT && op.previousValues) {
      const oldCustomType = op.previousValues['location'] ?? 'null';
      const newCustomType = op.opData['location'] ?? 'null';
      const metadata = op.metadata; // Access metadata here
      // Compare oldCustomType and newCustomType to determine what changed
      // Use metadata as needed as you process the upload
    }
    ```
  </Tab>

  <Tab title="Dart">
    ```dart theme={null}
    // Full replacement (basic):
    await db.execute('UPDATE todos set location = ?, _metadata = ? WHERE id = ?', [
      '("1234 Update Street",Denver,CO,80212)',
      'op-metadata-example', // Example metadata value
      'faffcf7a-75f9-40b9-8c5d-67097c6b1c3b'
    ]);

    // Diffing columns in uploadData (advanced):
    // See details about these advanced schema options at the end of this page
    if (op.op == UpdateType.put && op.previousValues != null) {
      final oldCustomType = op.previousValues['location'] ?? 'null';
      final newCustomType = op.opData['location'] ?? 'null';
      final metadata = op.metadata; // Access metadata here
      // Compare oldCustomType and newCustomType to determine what changed
      // Use metadata as needed as you process the upload
    }
    ```
  </Tab>

  <Tab title=".NET">
    ```csharp theme={null}
    // Full replacement (basic):
    await db.Execute(
      "UPDATE todos SET location = ?, _metadata = ? WHERE id = ?",
      new object[] { "(\"1234 Update Street\",Denver,CO,80212)", "op-metadata-example", "faffcf7a-75f9-40b9-8c5d-67097c6b1c3b" }
    );

    // Diffing columns in UploadData (advanced):
    // See details about these advanced schema options at the end of this page
    if (op.Op.ToString() == "PUT" && op.PreviousValues != null)
    {
      var oldCustomType = op.PreviousValues.GetValueOrDefault("location", "null")?.ToString() ?? "null";
      var newCustomType = op.OpData.GetValueOrDefault("location", "null")?.ToString() ?? "null";
      var metadata = op.Metadata; // Access metadata here
      // Compare oldCustomType and newCustomType to determine what changed
      // Use metadata as needed as you process the upload
    }
    ```
  </Tab>

  <Tab title="Rust">
    Example not yet available.
  </Tab>
</Tabs>

## Bonus: Mashup

What if we had a column defined as an array of custom types, where a field in the custom type was JSON? Consider the following Postgres schema:

```sql theme={null}
-- define custom type
CREATE TYPE extended_location AS (
    address_label text,
    json_address json
);

-- add column
ALTER TABLE todos
ADD COLUMN custom_locations extended_location[];
```

## Advanced Schema Options to Process Writes

With arrays and JSON fields, it's common for only part of the value to change during an update. To make handling these writes easier, you can enable advanced schema options that let you track exactly what changed in each row—not just the new state.

* `trackPreviousValues` (or `trackPrevious` in our JS SDKs): Access previous values for diffing JSON or array fields. Accessible later via `CrudEntry.previousValues`.
* `trackMetadata`: Adds a `_metadata` column for storing custom metadata. Value of the column is accessible later via `CrudEntry.metadata`.
* `ignoreEmptyUpdates`: Skips updates when no data has actually changed.

<Note>
  These advanced schema options were introduced in the following SDK versions:

  * Flutter v1.13.0
  * React Native v1.20.1
  * JavaScript/Web v1.20.1
  * Kotlin v1.1.0
  * Swift v1.1.0
  * Node.js v0.4.0
  * .NET v0.0.6-alpha.1
</Note>