After we surveyed the market, we noticed the necessity for an answer that would carry out quick SQL queries on fluid JSON knowledge, together with arrays and nested objects:

The Problem of SQL on JSON

Some type of ETL to remodel JSON to tables in SQL databases could also be workable for primary JSON knowledge with fastened fields which might be identified up entrance. Nonetheless, JSON with nested objects or new fields that “can spring up each 2-4 weeks,” as the unique Stack Overflow poster put it, is unattainable to deal with in such a inflexible method.

Relational databases supply various approaches to accommodate extra complicated JSON knowledge. SQL Server shops JSON in varchar columns, whereas Postgres and MySQL have JSON knowledge sorts. In these situations, customers can ingest JSON knowledge with out conversion to SQL fields, however take a efficiency hit when querying the info as a result of these columns assist minimal indexing at greatest.

SQL on Nested JSON Utilizing Rockset

With a number of fields that change, get added/eliminated, and many others, it may be reasonably cumbersome to take care of ETL pipelines. Rockset was designed to assist with this downside—by indexing all fields in JSON paperwork, together with all sort info, and exposing a SQL API on high of it.

For instance, with a Rockset assortment named new_collection, I can begin by including a single doc to an empty assortment that appears like:

{
    "my-field": "doc1",
    "my-other-field": "some textual content"
}

… after which question it.

rockset> choose "my-field", "my-other-field" 
         from new_collection;

+------------+------------------+
| my-field   | my-other-field   |
|------------+------------------|
| doc1       | some textual content        |
+------------+------------------+

Now, if a brand new JSON doc is available in with some new fields – perhaps with some arrays, nested JSON objects, and many others, I can nonetheless question it with SQL.

{
    "my-field": "doc2",
    "my-other-field":[
        {
            "c1": "this",
            "c2": "field",
            "c3": "has",
            "c4": "changed"
        }
    ]
}

I add that to the identical assortment and might question it simply as earlier than.

rockset> choose "my-field", "my-other-field" 
         from new_collection;

+------------+---------------------------------------------------------------+
| my-field   | my-other-field                                                |
|------------+---------------------------------------------------------------|
| doc1       | some textual content                                                     |
| doc2       | [{'c1': 'this', 'c2': 'field', 'c3': 'has', 'c4': 'changed'}] |
+------------+---------------------------------------------------------------+

I can additional flatten nested JSON objects and array fields at question time and assemble the desk I wish to get to – with out having to do any transformations beforehand.

rockset> choose mof.* 
         from new_collection, unnest(new_collection."my-other-field") as mof;

+------+-------+------+---------+
| c1   | c2    | c3   | c4      |
|------+-------+------+---------|
| this | subject | has  | modified |
+------+-------+------+---------+

Along with this, there’s robust sort info saved, which implies I will not get tripped up by having combined sorts, and many others. Including a 3rd doc:

{
    "my-field": "doc3",
    "my-other-field":[
        {
            "c1": "unexpected",
            "c2": 99,
            "c3": 100,
            "c4": 101
        }
    ]
}

It nonetheless provides my doc as anticipated.

rockset> choose mof.* 
         from new_collection, unnest(new_collection."my-other-field") as mof;

+------------+-------+------+---------+
| c1         | c2    | c3   | c4      |
|------------+-------+------+---------|
| sudden | 99    | 100  | 101     |
| this       | subject | has  | modified |
+------------+-------+------+---------+

… and the fields are strongly typed.

rockset> choose typeof(mof.c2) 
         from new_collection, unnest(new_collection."my-other-field") as mof;

+-----------+
| ?typeof   |
|-----------|
| int       |
| string    |
+-----------+

If having the ability to run SQL on complicated JSON, with none ETL, knowledge pipelines, or fastened schema, sounds fascinating to you, you need to give Rockset a attempt.