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

Multi-Structured Data In U-SQL

Kevin Feasel

2017-09-06

JSON, U-SQL

Melissa Coates shows us how to use U-SQL to normalize JSON files in which different rows may have differing structures: Handling the varying formats in U-SQL involves a few steps if it’s the first time you’ve done this: Upload custom JSON assemblies  [one time setup] Create a database   [one time setup] Register custom JSON assemblies […]

Read More

Convert SSAS Tabular Processing Scripts Into Tables

Chris Koester shows how to take an Analysis Services Tabular processing script in TMSL format and turn it into a table using OPENJSON: The previous post looked at how to process SSAS Tabular models with TMSL. Since SQL Server adds new JSON capabilities in 2016, let’s look at how to convert TMSL JSON to a Table […]

Read More

Categories

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