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

# Working with EJSON

> Learn how to work with MongoDB's Extended JSON (EJSON) format when using the MongoDB Connector with Ditto

<Info>
  EJSON support is available when using the MongoDB Connector to synchronize data between Ditto and MongoDB databases.
  Read more about EJSON in the MongoDB Connector [EJSON Mode](/cloud/mongodb-connector#ejson-mode) section.
</Info>

## What is EJSON?

Extended JSON (EJSON) is MongoDB's canonical representation of BSON (Binary JSON) data in JSON format. When EJSON mode is enabled in the MongoDB Connector, all BSON data types from MongoDB are converted to their canonical JSON representation before being stored in Ditto, and vice versa.

This ensures complete fidelity of MongoDB's rich data types when synchronizing with Ditto, but requires special handling in your application code and queries.

## EJSON Data Type Mappings

When EJSON mode is enabled, MongoDB BSON types are converted to their canonical representations:

### Common Type Conversions

| MongoDB BSON | EJSON Canonical Format                                        | Example                                                                        |
| ------------ | ------------------------------------------------------------- | ------------------------------------------------------------------------------ |
| Date         | `{ "$date": { "$numberLong": "<millis>" } }`                  | `{ "createdAt": { "$date": { "$numberLong": "1704067200000" } } }`             |
| ObjectId     | `{ "$oid": "<hex string>" }`                                  | `{ "_id": { "$oid": "507f1f77bcf86cd799439011" } }`                            |
| NumberLong   | `{ "$numberLong": "<string>" }`                               | `{ "count": { "$numberLong": "9223372036854775807" } }`                        |
| NumberInt    | `{ "$numberInt": "<string>" }`                                | `{ "age": { "$numberInt": "42" } }`                                            |
| Double       | `{ "$numberDouble": "<string>" }`                             | `{ "price": { "$numberDouble": "19.99" } }`                                    |
| Decimal128   | `{ "$numberDecimal": "<string>" }`                            | `{ "total": { "$numberDecimal": "123.456789" } }`                              |
| Binary       | `{ "$binary": { "base64": "<base64>", "subType": "<hex>" } }` | `{ "data": { "$binary": { "base64": "YmluYXJ5IGRhdGE=", "subType": "00" } } }` |
| Timestamp    | `{ "$timestamp": { "t": <uint32>, "i": <uint32> } }`          | `{ "ts": { "$timestamp": { "t": 1704067200, "i": 1 } } }`                      |

<Note>
  For a complete list of EJSON type representations, refer to the [MongoDB Extended JSON documentation](https://www.mongodb.com/docs/manual/reference/mongodb-extended-json/).
</Note>

## Querying EJSON Data in DQL

When EJSON mode is enabled, you need to account for the nested structure of EJSON types in your DQL queries.

### Working with Numbers

Numbers in EJSON are stored as strings, which affects comparison operations:

```sql theme={null}
-- Without EJSON
SELECT *
FROM products
WHERE price > 100

-- With EJSON - need to convert string to number
SELECT *
FROM products
WHERE CAST(price.`$numberDouble` AS DOUBLE) > 100
```

### Accessing Date Fields

Instead of directly accessing a date field, you need to traverse the EJSON structure:

```sql theme={null}
-- Without EJSON
SELECT *
FROM orders
WHERE orderDate > '2024-01-01'

-- With EJSON - accessing the nested date value
SELECT *
FROM orders
WHERE orderDate.`$date`.`$numberLong` > '1704067200000'
```

This type of direct string comparison will work correctly for all dates between `09/09/2001, 1:46:40 am` and `05/18/2033, 3:33:20 am`, as they fit in the range of 1000000000 to 2000000000.
If you are using dates outside of this range, you will need to use the `CAST` function to convert the date to a number.

```sql theme={null}
SELECT *
FROM orders
WHERE CAST(orderDate.`$date`.`$numberLong` AS DOUBLE) > 1704067200000
```

<Warning>
  Converting EJSON numeric strings (using `CAST` or other conversion functions) currently makes queries **ineligible for index optimization** in DQL. This can significantly impact query performance on large datasets.
</Warning>

## Updating EJSON Data

When updating EJSON data, you need to ensure that the data is updated in the correct format.
Typically this means ensuring that the nested fields are updated correctly using strings to represent numeric values.

```sql theme={null}
UPDATE orders
SET orderDate.`$date`.`$numberLong` = '1704067200000'
WHERE _id = 'my-id'
```

### Changing Datatypes

We recommend avoiding changing the datatype of an EJSON field, aside from moving from a null type to a non-null type.

For example, if you want to change the order date from a null type to a date type, you can do the following:

```sql theme={null}
-- Changing order date from a null type to a date type
UPDATE orders
SET orderDate.`$date`.`$numberLong` = '1704067200000'
WHERE _id = 'my-id'
```

If you do wish to change the datatype of an EJSON field (e.g. Int32 to a Double), you can do the following:

```sql theme={null}
-- Changing order counter from a Int32 to a Double
UPDATE orders
SET orderCounter.`$numberDouble` = '1704067200000'
UNSET orderCounter.`$numberInt`
WHERE _id = 'my-id'
```

## Best Practices for EJSON Queries

### 1. Minimize Type Conversions

Avoid converting EJSON numeric types unless absolutely necessary, as this prevents index usage:

```sql theme={null}
-- ❌ Bad: Converting for comparison (no index usage on amount.$numberDouble)
SELECT *
FROM orders
WHERE CAST(amount.`$numberDouble` AS DOUBLE) > 100
```

### 2. Use String Comparisons When Possible

For some use cases, string comparison of EJSON numeric values works correctly:

```sql theme={null}
-- Works for equality checks
SELECT *
FROM products
WHERE price.`$numberDouble` = '19.99'

-- Works for lexicographic comparison of integers with same digit count
SELECT *
FROM items
WHERE year.`$numberInt` >= '2020' 
  AND year.`$numberInt` <= '2024'

-- Works for date range queries between 09/09/2001, 1:46:40 am and 05/18/2033, 3:33:20 am
SELECT *
FROM orders
WHERE orderDate.`$date`.`$numberLong` > '1704067200000'
```

### 3. Create Helper Fields

If you frequently query EJSON fields, consider creating flattened helper fields that are not stored as BSON:

```sql theme={null}
-- Original document with EJSON
{
  "createdAt": { "$date": { "$numberLong": "1704067200000" } },
  "createdAtMillis": 1704067200000  // Helper field for efficient queries
}

-- Now you can query efficiently
SELECT *
FROM events
WHERE createdAtMillis > 1704000000000
```

## Converting EJSON in Your Application

When working with EJSON data in your application (e.g. in your UIs or business logic), you'll need to convert between EJSON and native types using platform-specific BSON libraries.
You should pass the EJSON data contained in your DittoQueryResultItem to the BSON library to convert it to the native type.

### Platform-Specific BSON Libraries

BSON libraries are available for most platforms, you can find a suggested (though not exhaustive) list of libraries below:

| Platform           | Library                                                             |
| ------------------ | ------------------------------------------------------------------- |
| Swift              | [swift-bson](https://github.com/mongodb/swift-bson)                 |
| Kotlin             | [bson](https://github.com/mongodb/kbson)                            |
| JavaScript/Node.js | [bson](https://github.com/mongodb/js-bson)                          |
| Flutter            | [bson](https://pub.dev/packages/bson)                               |
| Java               | [MongoDB Java Driver](https://mongodb.github.io/mongo-java-driver/) |
| C#                 | [MongoDB.Bson](https://www.nuget.org/packages/MongoDB.Bson/)        |
| Rust               | [bson](https://docs.rs/bson/latest/bson/)                           |

## Performance Considerations

### Index Eligibility

<Warning>
  Queries that use type conversion functions (like `CAST`) on EJSON fields **cannot use indexes**. This can severely impact query performance for larger datasets.
</Warning>

To maintain query performance with EJSON:

1. **Avoid conversions in WHERE clauses**: Design queries that work with EJSON string representations
2. **Pre-compute values**: Store additional fields with converted values for frequently queried data
3. **Use compound strategies**: Combine indexed fields with post-filtering for complex queries

### Query Optimization Examples

```sql theme={null}
-- ❌ Poor performance: Type conversion prevents index usage
SELECT *
FROM orders 
WHERE CAST(total.$numberDouble AS DOUBLE) > 1000

-- ✅ Better: Use a pre-computed field
-- Document structure:
{
  "total": { "$numberDouble": "1234.56" },
  "totalIndexed": 1234.56  // Indexed field
}

SELECT *
FROM orders
WHERE totalIndexed > 1000

-- ✅ Alternative: Range query with string comparison (for specific use cases)
SELECT * FROM orders 
WHERE total.`$numberDouble` >= '1000.00' 
  AND total.`$numberDouble` < '2000.00'
```

## Troubleshooting Common Issues

<AccordionGroup>
  <Accordion title="Query Performance Degradation">
    **Symptom**: Queries that were fast are now slow after enabling EJSON mode.

    **Solution**: Review queries for type conversions and create indexed helper fields for frequently queried EJSON data.
  </Accordion>

  <Accordion title="Numeric Comparison Failures">
    **Symptom**: Numeric comparisons return unexpected results.

    **Solution**: Remember that EJSON numbers are strings. Use `CAST` for accurate comparisons (accepting the performance trade-off) or implement application-level filtering.
  </Accordion>

  <Accordion title="Date Queries Not Working">
    **Symptom**: Date range queries return no results.

    **Solution**: Access the nested `$date.$numberLong` field and compare as milliseconds since epoch:

    ```sql theme={null}
    WHERE orderDate.`$date`.`$numberLong` >= '1704067200000'
    ```
  </Accordion>
</AccordionGroup>

## Related Resources

* [MongoDB Connector Configuration](/cloud/mongodb-connector)
* [MongoDB Extended JSON Specification](https://www.mongodb.com/docs/manual/reference/mongodb-extended-json/)
* [DQL Query Reference](/dql/dql)
* [Ditto Data Types](/sdk/latest/crud/create#data-types)