Xata's JSON Column Type
Tags: schema, database, json, sql
Published: 2023-10-17 17:33:51

Data models that use schemas are great! At Xata, we believe they're a solid choice in most scenarios. But, we also know that not every piece of data fits perfectly into the relational model or is not as convenient as schemaless, and sometimes, especially in the early stages of a project, you want something more flexible and straightforward. That's where using JSON documents within a relational data store comes in handy. It offers the best of both worlds – structure when you need it and a bit of freedom when you don't.

Many of our users have been [asking for this feature](https://xata.canny.io/feature-requests/p/json-objects), and as part of [launch week](/blog/launch-week-august-2023) we are happy to announce that it's finally here.

Basic support for [JSON column type](https://xata.io/docs/sdk/filtering#json) has been added and it will bring many benefits including:

- **Enhanced flexibility** by providing a way to store schemaless data in a relational database
- **Data integrity** with JSON validation according to [RFC 7159](https://www.rfc-editor.org/rfc/rfc7159.html)
- **Streamlined development** that stores any unstructured data directly with no need to handle a schema or data conversions
- **Efficient queries** so you can apply many filters to nested nodes
- **Search functionality**, as JSON documents are indexed as any other Xata data type and can be searched using the [full-text search](https://xata.io/docs/sdk/search) capabilities of Xata

We plan on extending Xata support for JSON even further in the future, but the current capabilities are already very powerful and will solve most use cases.
Below we provide examples of how you can start using JSON documents in Xata today.

Let's think a bit about a simple data model for an online shop. Suppose we have a _Products_ table.


![Products table](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/ix0wnx8wyb2vgoic6po6.png)


## Creating a JSON column

Sometimes different categories of products have completely different specs, so we don't want to create a column for each of them.

We can use a JSON column to store the product details. Let's do this by adding a `details` field to our table. You can do this via the UI or via the API like this:

```jsonc
// POST https://{workspace}.{region}.xata.sh/db/{db}:{branch}/tables/{table}/columns

{
  "name": "details",
  "type": "json"
}
```

Let's add a few products with different details, for instance:

- A t-shirt with size and color
- A book with author, ISBN and number of pages
- A climbing rope with a length and a thickness



>> #### TypeScript
```
const record1 = await xata.db.Products.create({
  name: 'Xata xwag T-shirt',
  details: {
    color: 'purple',
    size: 'M',
  }
});
const record2 = await xata.db.Products.create({
  name: 'Meditations',
  details: {
    author: 'Marcus Aurelius',
    isbn: '978-0140449334',
    pages: 304
  }
});
const record3 = await xata.db.Products.create({
  name: 'Long climbing rope',
  details: {
    length: 80,
    thickness: 9.8,
    color: 'blue',
  }
});
```

>> #### Python
```
record1 = xata.records().insert("Products", {
  "name": "Xata xwag T-shirt",
  "details": {
    "color": "purple",
    "size": "M",
  }
})
record2 = xata.records().insert("Products", {
  "name": "Meditations",
  "details": {
    "author": "Marcus Aurelius",
    "isbn": "978-0140449334",
    "pages": 304
  }
})
record3 = xata.records().insert("Products", {
  "name": "Long climbing rope",
  "details": {
    "length": 80,
    "thickness": 9.8,
    "color": "blue"
  }
})
```

>> #### JSON
```jsonc
// POST https://{workspace}.{region}.xata.sh/db/{db}:{branch}/tables/{table}/data
{
  "name": "Xata xwag T-shirt",
  "details": "{\"color\": \"purple\", \"size\": \"M\"}"
}

{
  "name": "Meditations",
  "details": "{\"author\": \"Marcus Aurelius\", \"isbn\": \"978-0140449334\", \"pages\": 304}"
}

{
  "name":"Long climbing rope",
  "details":"{\"length\": 80, \"thickness\": 9.8, \"color\": \"blue\"}"
}
```

It's important to note that the JSON documents are processed and stored in a binary format in order to improve querying and storage performance.
This has the following implications:

- White spaces are not preserved
- Key order is not preserved
- In case of duplicate key, only the last one is stored

_________________________________________________________
## Querying JSON documents

### The arrow notation `->`

This is PostgreSQL's syntax for navigating JSON fields. It's used to access the value of any JSON node, no matter how deep in the tree.

Xata uses a similar notation to query data and apply some of the existing filters to any JSON value. PostgreSQL uses different operators and casting
depending on the data types, but Xata is able to infer the data type from the provided value and apply the correct operator.

So far, comparison by strings and numbers is supported but this will be extended in the near future.


__________________________________________
### Filter products size 'M'
__________________________________________

>> #### TypeScript
```
const records = await xata.db.Products.filter({
  "details->size": 'M'
}).getMany();
```

>> #### Python
```
records = xata.data().query("Products", {
  "filter": {
    "details->size": "M"
  }
})
```

>> #### SQL
```
// POST https://{workspace}.{region}.xata.sh/db/{db}:{branch}/sql

{
  "statement": "SELECT * FROM \"Products\" WHERE details->>'size' = 'M';"
}
```

>> #### JSON
```
// POST https://{workspace}.{region}.xata.sh/db/{db}:{branch}/tables/{table}/query

{
  "filter": {
    "details->size": "M"
  }
}
```

__________________________________________________
### Filter products with a length greater than 50 meters
__________________________________________________

>> #### TypeScript
```
const records = await xata.db.Products.filter({
  "details->length": {
    "$gt": 50
  }
}).getMany();
```

>> #### Python
```
records = xata.data().query("Products", {
  "filter": {
    "details->length": {
      "$gt": 50
    }
  }
})
```

>> #### SQL
```
// POST https://{workspace}.{region}.xata.sh/db/{db}:{branch}/sql

{
  "statement": "SELECT * FROM \"Products\" WHERE (details->>length)::numeric > 50;"
}
```

>> #### JSON
```
// POST https://{workspace}.{region}.xata.sh/db/{db}:{branch}/tables/{table}/query

{
  "filter": {
    "details->length": {
      "$gt": 50
    }
  }
}
```
_____________________________________________
### Check for a substring in a nested JSON node
______________________________________________

>> #### TypeScript
```
const records = await xata.db.Products.filter({
  "details->author": {
    "$contains": "Marcus"
  }
}).getMany();
```

>> #### Python
```
records = xata.data().query("Products", {
  "filter": {
    "details->author": {
      "contains": "Marcus"
    }
  }
})
```

>> #### SQL
```
// POST https://{workspace}.{region}.xata.sh/db/{db}:{branch}/sql
{
  "statement": "SELECT * FROM \"Products\" WHERE details->>author LIKE '%Marcus%';"
}
```

>> #### JSON
```
// POST https://{workspace}.{region}.xata.sh/db/{db}:{branch}/tables/{table}/query
{
  "filter": {
    "details->author": {
      "$contains": "Marcus"
    }
  }
}
```

________________________________
### Other general control or negation operators work as well
_________________________________

>> #### TypeScript
```ts
const records = await xata.db.Products.filter({
    "$not": {
        "details->length": {
            "$gt": 50
        }
    }
}).getMany();
```

>> #### Python
```
records = xata.data().query("Products", {
    "filter": {
        "$not": {
            "details->length": {
                "$gt": 50
            }
        }
    }
})
```

>> #### SQL
```
// POST https://{workspace}.{region}.xata.sh/db/{db}:{branch}/sql
{
  "statement": "SELECT * FROM \"Products\" WHERE NOT (details->>length)::numeric > 50;"
}
```

>> #### JSON
```
// POST https://{workspace}.{region}.xata.sh/db/{db}:{branch}/tables/{table}/query
{
  "filter": {
    "$not": {
      "details->length": {
        "$gt": 50
      }
    }
  }
}
```

________________________________________________
## Conclusion

Xata is committed to simplifying the way you work with data. We will keep improving our offering by both adding more rich data types and extending the capabilities of the current ones.
Basic JSON support is one more step in that direction along with the previously released [files attachments](https://xata.io/blog/file-attachments).

If you have feedback or questions, you can reach out to us on [Discord](https://xata.io/discord) or [X / Twitter](https://twitter.com/xata).