MicrosoftThis sounds awesome ...but, will it support indexing/querying in json arrays ?
Because a use case could be replacing a child table with a json field. Imagine an Order table that has an OrderLines childtable (with columns OrderId/ProductId/Amount/Price), linking the Order table to the Product table .
One could eliminate the OrderLines table, and replace it with an OrderLinesJson as a json field in the Order table. But that means such a json would an array with multiple OrderLines containing ProductId and Price/Amount :
[ {"ProductId" : 1, "Price" : 5.0, "Amount" : 2},
{"ProductId" : 2, "Price" : 10.0, "Amount" : 1},
{"ProductId" : 5, "Price" : 11.5, "Amount" : 4},
..... ]
Now imagine then you want to query for all Orders that have an OrderLine for ProductId X with a Amount bigger then Y
This use case of replacing child tables is actually on the MS website but i find no info on how to query/index that : https://learn.microsoft.com/en-us/sql/relational-databases/json/json-data-sql-server?view=sql-server-ver17
JSON support in SQL Server and Azure SQL Database lets you combine relational and NoSQL concepts. You can easily transform relational to semi-structured data and vice-versa. JSON isn't a replacement for existing relational models, however. Here are some specific use cases that benefit from the JSON support in SQL Server and in SQL Database.
Consider denormalizing your data model with JSON fields in place of multiple child tables.
