Introducing the JSON column type

Use the new JSON column type for improved data storage in Xata.

Written by

Alejandro Martínez

Published on

September 1, 2023

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, and as part of launch week we are happy to announce that it's finally here.

Basic support for JSON column type 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
  • 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 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
Products table

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:

// 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
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',
  }
});

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

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.

const records = await xata.db.Products.filter({
  "details->size": 'M'
}).getMany();
const records = await xata.db.Products.filter({
  "details->length": {
    "$gt": 50
  }
}).getMany();
const records = await xata.db.Products.filter({
  "details->author": {
    "$contains": "Marcus"
  }
}).getMany();
const records = await xata.db.Products.filter({
    "$not": {
        "details->length": {
            "$gt": 50
        }
    }
}).getMany();

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.

If you have feedback or questions, you can reach out to us on Discord or X / Twitter.

Subscribe to our blog

Join our community of subscribers to stay up to date with the latest news, tips and thought leadership, delivered directly to your inbox.