Indexing JSON

Kevin Feasel

2015-12-25

JSON

Jovan Popovic answers a question which has been on my mind:  how are we supposed to index JSON data in SQL Server 2016?

In this post I will show how you can add indexes on JSON properties in product catalog. In SQL Server 2016, you can use two type of indexes on JSON text:

  1. Index on computed column that index some specific properties in JSON.
  2. Full text search index that can index all key:value pairs in JSON objects.

This is the downside to JSON not being an official type:  indexing is somewhat limited.  In comparison, you could create XML indexes which were specially-designed to do the job of searching for text within an XML field.

Related Posts

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

JSON Output And SSIS

Stacia Varga works around an oddity in the way SSIS reads JSON outputs: What happened? The T-SQL produces the correct results in SQL Server Management Studio (SSMS). However, in SSIS, the same T-SQL statement in an OLE DB Source in a Data Flow Task produces two rows of data which adds a line feed into the […]

Read More

Categories

December 2015
MTWTFSS
« Nov Jan »
 123456
78910111213
14151617181920
21222324252627
28293031