JSON Column Type In Xata IO

Aim of adding Column Types in Xata Database by their team:

Data models that use schemas are great. At Xata, they believe they’re a solid choice in most scenarios. But, they also know that not every piece of data fits perfectly into the relational model or is not as convenient as schema less, 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.

Pros of JSON Types:

• Enhanced flexibility by providing a way to store schema less 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

Steps to add JSON column in your database:

1. First create an account in Xata IO for free
2. Go to workspaces
3. Create a Workspace and a Database by clicking on + Add Database

Give your DB a name and click on create

Open your DB

Click on Add a table and then on Add an empty table
Give your table a name of your choice, I am going to give it a name of “test”.

Click on the + icon add create two columns
1) name — type: String
2) details — type: JSON

Again click on the + icon and in the same way, create a column for the JSON Type, just remember to chose JSON as the column type, not String.

We will add data in our columns by using the Xata playground created by their team.

On the left navigation bar, Click on Playground and select TypeScript as your language.
You can do this by changing your language to Python or PostgreSQL
We are going to use TypeScript in this tutorial.

I am having some dummy code which you can use for demo purposes:

// Generated with CLI
import { getXataClient } from "./xata";
const xata = getXataClient();

const record1 = await xata.db.test.create({
  name: "Hero",
  details: {
    fav_color: "Gold",
    fav_number: 10,
    isAdult: false,
  },
});
const record2 = await xata.db.test.create({
  name: "Andrew",
  details: {
    fav_color: "Silver",
    fav_number: 100,
    isAdult: true,
  },
});
const record3 = await xata.db.test.create({
  name: "Jack",
  details: {
    fav_color: "Bronze",
    fav_number: 900,
    isAdult: true,
    role: "ADMIN",
  },
});

// Filter Commands

const getLesserThanRecords = await xata.db.test
  .filter({
    "details->fav_number": { $lt: 50 },
  })
  .getMany();

const getGreaterThanRecords = await xata.db.test
  .filter({
    "details->fav_number": { $gt: 50 },
  })
  .getMany();

console.log(getGreaterThanRecords);
// console.log(getLesserThanRecords);

Let me summarize code for you :)
Constants: record1 , record2 and record3 are going to insert the data in our columns

Get Hero’s stories in your inbox

Join Medium for free to get updates from this writer.

Remember me for faster sign in

Constants: getLesserThanRecords and getGreaterThanRecords are the commands we are using to filter our data.

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.

details->fav_number : Here it means to look inside the details column and find the fav_number field and find the numbers greater than $gt or lesser than $lt value.

Click on Run Code at the top right corner and see the output!!
Output should come like this:

Done!!

If you haven’t read my previous blogs which too are on Xata, have a read:
How CSV files work in Xata
File Attachments In Xata

Much more coming soon!!
Till that have a look at the Xata Docs for more such interesting things !

I am a freelancer & content-writer

Link of my portfolio

Follow me on medium for more such superb blogs :)

Any doubts?
Contact me:
Discord: hero.xyz
Twitter

I hope you’ve enjoyed reading it.

Will be back soon with another blog, to get updated with my blogs follow me on medium or subscribe to the newsletter.

Till that time, have a nice day ahead, and always keep smiling. :)

Thank You,

Hero