JSON In SQL 2016

Kevin Feasel



Jovan Popovic has a couple of posts on JSON.  First, using OPENJSON to generate a tally table:

Problem: I want to dynamically generate a table of numbers (e.g. from 0 to N). Unfortunately we don’t have this kind of function in SQL Server 2016, but we can use OPENJSON as a workaround.

OPENJSON can parse array of numbers [1,25,3,5,32334,54,24,3] and return a table with [key,value] pairs. Values will be elements of the array, and keys will be indexes (e.g. numbers from 0  to length of array – 1). In this example I don’t care about values I just need indexes.

Well, that’s one way to do it.

Also, Jovan talks about performance of FOR JSON PATH:

You might notice that table scans take majority of the query cost. Cost of the FOR JSON (JSON SELECT operator) is 0% compared to others. Also, since we are joining small tables (one sales order and few details), cost of the JOIN is minor. Therefore, if you processing small requests there will be no performance difference between formatting JSON on client side and in database layer.

This comment was actually due to a bug in the AdventureWorks CTP 3 database.  The good news is that JSON isn’t obviously slow performance problems, but I’d like to see some more thorough performance tests.

Both posts via Database Weekly.

Related Posts


Greg Low walks us through a mechanism to check whether data has changed: In a previous post, I wrote about how to determine if a set of incoming values for a row are different to all the existing values in the row, using T-SQL in SQL Server. I later remembered that I’d seen a message by Adam […]

Read More

Flattening JSON Data With Databricks

Ivan Vazharov gives us a Databricks notebook to parse and flatten JSON using PySpark: With Databricks you get: An easy way to infer the JSON schema and avoid creating it manually Subtle changes in the JSON schema won’t break things The ability to explode nested lists into rows in a very easy way (see the […]

Read More


November 2015
« Jan Dec »