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.
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
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
// 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
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.
If you have feedback or questions, you can reach out to us on Discord or X / Twitter.
Top comments (0)