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

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:

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

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"
  }
})

    // Not yet available

// 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'

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

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

// POST https://{workspace}.{region}.xata.sh/db/{db}:{branch}/sql
 
{
  "statement": "SELECT * FROM \"Products\" WHERE details->>'size' = 'M';"
}

// 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

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

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

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

// 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

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

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

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

// 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

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

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

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

// 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.

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