Flattening a JSON Object So It’s Queryable Utilizing Rockset

0/5 No votes

Report this app



Many builders use NoSQL databases in an effort to ingest unstructured and schemaless knowledge. With regards to understanding the information by writing queries that be a part of, mixture, and search, it turns into more difficult. That is the place Rockset turns into a terrific companion not solely in understanding your unstructured knowledge however in returning queries that be a part of, mixture, 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 knowledge lakes, knowledge streams, transactional databases, and knowledge warehouses.

flattening json objects

On this twitch stream, we created a MongoDB Atlas occasion. After the occasion is created, you may have 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 motion pictures.

snapshot mongodb

After we configure the occasion, we created an integration on Rockset with MongoDB, by utilizing the built-in knowledge 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 might be discovered right here — or you’ll be able to watch the stream beneath!

Inspecting the information

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

Embedded content material: https://gist.github.com/nfarah86/ef1cc9da88e56226c4c46fd0e3c8e16e

For those who observed the sector genres appears to be like like this:

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

… Strings, Strings, in all places…


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

Remodeling Genres from a JSON String → to an ARRAY


Rockset has a operate referred to as UNNEST, that can be utilized to broaden 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 will accomplish this in 2 steps:

  1. Parse the given string as JSON:

Right here, you should utilize 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, it’s best to get this again:

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

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

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

  1. Develop the array and flatten the JSON object:


UNNEST(yourCollectionName.genres AS worth) AS genres

Once you run this question, it’s best to get:

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

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

Embedded content material: https://www.youtube.com/watch?v=AtCcXrtgQCg&checklist=PLinxPR8yVNSLjolgZUr1XU9VAoES3iMuX&index=7

TLDR: yow will discover all of the assets it is advisable to get began on Rockset within the developer nook.


Leave a Reply

Your email address will not be published.

This site uses Akismet to reduce spam. Learn how your comment data is processed.