DEV Community

Cover image for Xata's JSON Column Type
Xata profile image Joan
Joan for Xata

Posted on • Updated on • Originally published at xata.io

Xata's JSON Column Type

#schema #database #json #sql

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"
}
Enter fullscreen mode Exit fullscreen mode

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',
  }
});
Enter fullscreen mode Exit fullscreen mode

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"
  }
})
Enter fullscreen mode Exit fullscreen mode

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\"}"
}
Enter fullscreen mode Exit fullscreen mode

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();
Enter fullscreen mode Exit fullscreen mode

Python 

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

SQL 

// POST https://{workspace}.{region}.xata.sh/db/{db}:{branch}/sql

{
  "statement": "SELECT * FROM \"Products\" WHERE details->>'size' = 'M';"
}
Enter fullscreen mode Exit fullscreen mode

JSON 

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

{
  "filter": {
    "details->size": "M"
  }
}
Enter fullscreen mode Exit fullscreen mode

Filter products with a length greater than 50 meters

TypeScript 

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

Python 

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

SQL 

// POST https://{workspace}.{region}.xata.sh/db/{db}:{branch}/sql

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

JSON 

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

{
  "filter": {
    "details->length": {
      "$gt": 50
    }
  }
}
Enter fullscreen mode Exit fullscreen mode

Check for a substring in a nested JSON node

TypeScript 

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

Python 

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

SQL 

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

JSON 

// POST https://{workspace}.{region}.xata.sh/db/{db}:{branch}/tables/{table}/query
{
  "filter": {
    "details->author": {
      "$contains": "Marcus"
    }
  }
}
Enter fullscreen mode Exit fullscreen mode

Other general control or negation operators work as well

TypeScript 

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

Python 

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

SQL 

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

JSON 

// POST https://{workspace}.{region}.xata.sh/db/{db}:{branch}/tables/{table}/query
{
  "filter": {
    "$not": {
      "details->length": {
        "$gt": 50
      }
    }
  }
}
Enter fullscreen mode Exit fullscreen mode

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)

Read next

franckpachot profile image

PostgreSQL index Correlation with UPDATE

Franck Pachot -

dchuquilla profile image

Rails ActiveRecord count, size and length, map and pluck

Dario Chuquilla -

muhammadalhrake profile image

Understanding Database Normalization and the Importance of 1NF

Mohammad Alhraki -

nghiand1010 profile image

Bulk Insert, Bulk Delete, Bulk Update with Dapper by Bulk.Dapper

Nguyen Dinh Nghia -