Sunday, September 25, 2022
HomeBig DataFlattening a JSON Object So It’s Queryable Utilizing Rockset

Flattening a JSON Object So It’s Queryable Utilizing Rockset

Many builders use NoSQL databases with a purpose to ingest unstructured and schemaless information. On the subject of understanding the information by writing queries that be a part of, combination, and search, it turns into tougher. That is the place Rockset turns into a terrific companion not solely in understanding your unstructured information however in returning queries that be a part of, combination, and search inside milliseconds at scale. Rockset is a real-time indexing database constructed for the cloud that acts as an exterior indexing layer on prime of your information lakes, information streams, transactional databases, and information warehouses.

On this twitch stream, we created a MongoDB Atlas occasion. After the occasion is created, you’ve the choice to make use of the MongoDB preseeded databases. Right here I used the database referred to as netflix and the gathering referred to as films.

snapshot mongodb

After we configure the occasion, we created an integration on Rockset with MongoDB, by utilizing the built-in information connector for MongoDB. We offer restricted credentials, so Rockset can learn the information from MongoDB. The directions to configure Atlas and create the Rockset integration may be discovered right here — or you’ll be able to watch the stream under!

Inspecting the information

As soon as the information is in Rockset, it’ll look one thing like this:

Embedded content material:

If you happen to seen the sphere genres appears to be like like this:

"genres": "[{'id': 80, 'name': 'Crime'}]"

… Strings, Strings, all over the place…


Principally, we now have a string kind as a price, when it ought to be an array of objects. Let’s say you needed to see all of the style’s names with out the id key; you wouldn’t be capable to write a question that may do that, because it’s presently formatted.

Reworking Genres from a JSON String → to an ARRAY


Rockset has a perform referred to as UNNEST, that can be utilized to develop array of values or paperwork to be queried (aka flattening the JSON object). Assuming no errors in how genres is formatted as a string, we are able to accomplish this in 2 steps:

  1. Parse the given string as JSON:

Right here, you need to use JSON_PARSE, which parses a given JSON string as a JSON object:

SELECT JSON_PARSE("[{"id":3, "name":"thriller"}]");

Once you run that within the Question Editor, you must get this again:

-- get an array of objects again
[{"id":3, "name":"thriller"}]

Take into account, our string is presently formatted like this:

“[{'id': 80,'name': 'Crime'}]"

  1. Broaden the array and flatten the JSON object:


UNNEST(yourCollectionName.genres AS worth) AS genres

Once you run this question, you must get:

-- results of UNNEST the place we return genres.identify
[{"name": "Crime”}]

Within the following recorded twitch stream, we really bought a curveball ball 🎾, the place we couldn’t JSON_PARSE(genres). A parsing error was thrown as a result of the string within the information is malformatted. On this case, we added an additional step to resolve this. Try the stream 👇 to see how we resolved the error– (and don’t overlook to comply with us!)

Embedded content material:

TLDR: you could find all of the assets you’ll want to get began on Rockset within the developer nook.



Please enter your comment!
Please enter your name here

Most Popular