Welcome back to our blog series on SQL Server 2016! In our previous blog post, we discussed how to create a valid JSON output from relational tables. If you missed it, you can catch up here. Today, we will take a step further and explore the process of reading JSON documents using the OPENJSON built-in table value function.

OPENJSON is a powerful function that allows us to parse JSON text and return properties as a relational table. It can be used with explicitly defined schemas or with the default schema. In this blog post, we will focus on using the default schema.

Let’s start with a simple example:

SELECT * FROM OPENJSON('["India", "United Kingdom", "United States", "Mexico", "Singapore"]')

The output of this query will be a rowset view over the JSON document. Each element in the array will generate one row with an index and value. If the JSON expression contains an object, the (key, value) pairs will be properties on the first level.

Now, let’s take a look at a more complex JSON text:

DECLARE @json NVARCHAR(4000)
SET @json = N'{
  "id": 1,
  "name": "A green door",
  "price": 12.50,
  "color": "Green",
  "type": "bricks",
  "tags": ["home", "green"]
}'

SELECT * FROM OPENJSON(@json) AS MyJSON;
SELECT * FROM OPENJSON(@json, N'lax $.tags');

In this example, we are retrieving values from a complex JSON object using two different queries. The first query returns all the properties of the JSON object, while the second query specifically retrieves the values from the “tags” array.

It’s important to note that although OPENJSON and OPENXML may appear similar in usage, there are fundamental differences. OPENJSON accepts text as input, while OPENXML accepts a handle. Additionally, OPENJSON returns rows with a (key, value) schema, whereas OPENXML returns a flattened table with all nodes.

Now that we have covered the basics of using OPENJSON, stay tuned for future blog posts where we will explore more advanced usage of this powerful construct. If you have already started playing around with SQL Server 2016, we would love to hear about your experiences!

Thank you for reading and see you in the next blog post!