SQL vs. NoSQL: Solved With SingleStore’s Blazing-Fast JSON Analytics

People used to say — and some non-SingleStore customers still say — that databases can’t do transactions and analytics simultaneously.

They can, and we know this because we do it daily for SingleStore users. But that’s old news. That’s not what we’re here to write about.

We're here to tell you about our latest trick. Most venerable database elders will say it is impossible to get the benefits of SQL and NoSQL in the same database.

Our answer to this statement is: Yes. You. Can! Let us explain.

The best of both worlds

SQL is nice because the data is structured, allowing minimal reads, efficient joins and fast analytical queries. SQL is also more powerful than the query languages for NoSQL — but SQL is a major pain when it comes to making and editing tables and writing to them in their schemas.

On the other hand, NoSQL is nice because you don’t have to deal with all that nonsense. You’re writing an application, you have a JSON object and you say, “hey database, remember this,” and it does. Easy peasy. In the SQL world, they would tell you to update your SQL queries and schema every time a field is added, and maybe even ask the DBA for help changing the schema. When they tell you this, you are probably thinking, “Are you kidding me? Do you have any idea how often I add fields?”

The result is a scenario where you decide to use NoSQL and JSON. Then some time passes, and you want to run queries with analytics and group bys (or aggregations and $group stage in the Mongo NoSQL lingo) and… experience deep sadness. It's so slow. It's so hard to say what you want. But this cannot be helped, and as a result, it’s the Achilles heel of NoSQL.

What if we told you that SingleStore has solved this problem? You'd probably be skeptical. That's what people thought about transactions and analytics, though, and we proved otherwise — so let us explain how we do it.

SingleStore is the only application database in the world that automatically columnarizes JSON. When you store JSON in other databases, it more or less gets stored as a big blob of text. So when you want to query it, if you need to read a lot of rows (as you do in analytical queries), you have to read all those blobs, and it takes forever. In SingleStore, you can write:

1
SELECT
2
  details::$city AS city,
3
  AVG(details::trip::%fare) AS avg_fare
4
FROM
5
  ride_events
6
GROUP BY
7
  1;

And poof, you get the speed of a relational SQL query — which is only possible with a columnstore engine, but on JSON data. SingleStore only needs to read the city and fare details here. Plus, it uses a vectorized query engine, meaning it's hundreds of times faster (yes, hundreds) than NoSQL engines and even legacy SQL systems.

When you were writing your app, you said to SingleStore, “hey, remember this JSON blob,” and when you wanted to do analytics on it, it was blazing fast because SingleStore was smart enough to break up the JSON into columns and store it just like it would if you created a schema with separate columns for every field.

Upgrade to faster analytics on JSON data

If you find yourself on Postgres, MySQL, Elastic, OpenSearch, Databricks, Snowflake or any other SQL database and struggle to do fast analytics on JSON, stop. Move to SingleStore, and have the analytical speed of columnstore-based SQL and the ease of use of NoSQL and JSON in one place.

Now, we know what some of you are thinking. You are sad because you have MongoDB®. You have BSON, not JSON, and all those Mongo queries. You can’t translate them all. You don’t want to use tools to move the data. It’s all too hard. But how about this: don’t do any of that.

A better Mongo experience with SingleStore Kai™

With SingleStore Kai™️, our Mongo-compatible API, you can run your Mongo queries on SingleStore as is with zero modifications. We also natively support CDC from Mongo to SingleStore, so you don’t need any tools to move the data. We even support BSON (including the GIN Index on BSON), a Mongo fan favorite. Here’s the preceding query on SingleStore Kai:

1
db.ride_events.aggregate([
2
  {
3
    $group: {
4
      _id: "$details.city",
5
      avg_fare: { $avg: "$details.trip.fare" }
6
    }
7
  },
8
  {
9
    $project: {
10
      city: "$_id",
11
      avg_fare: 1,
12
      _id: 0
13
    }
14
  }
15
]);

This topic is timely, because the AI apps revolutionizing the world are only as capable as the data they consume. To build their insights, these apps demand data of all forms: highly structured tabular and loosely structured JSON documents. Plus, they mix in full text and vector data. Conventional wisdom might suggest you need a specialized database for each kind of data, making your application slow, convoluted or both. But it doesn't have to be that way! SingleStore can handle structured and semi-structured data. It supports vectors and text, too — all in one database.

To summarize:

If you are struggling with slow analytical queries with lots of joins and group bys on JSON data on any SQL database, SingleStore solves your problem without forcing you to abandon the ease of use benefits of storing your data in JSON.
If you are struggling with slow aggregations and $group stage queries on your document data in Mongo, SingleStore solves your problems without forcing you to rewrite queries, use tools to move the data or abandon the ease-of-use benefits of NoSQL.
If you want to improve performance of your AI application that relies on both SQL and NoSQL data, putting it all in SingleStore leads to consistent, predictable performance. We can also handle vector- and text-search-based queries for your RAG and AI agents.

SQL vs. NoSQL: solved. Why choose when you can have both! Start free with SingleStore today.