1

T-SQL has a couple of functions that can parse JSON data, but none of them are applicable to Azure SQL Data Warehouse.

I have data in JSON format in a VARCHAR column that looks like:

{'Purpose': 'Purpose 1', 'Activity': 'This activity'}

I could extract the 'Purpose' field in SQL Server by using the JSON_VALUE function: 

SELECT JSON_VALUE('{'Purpose': 'Purpose 1', 'Activity': 'This activity'}', '$.Purpose');

But this is not supported in SQL Data Warehouse. Is there a way to do this in SQL Data Warehouse?

https://learn.microsoft.com/en-us/sql/t-sql/functions/json-functions-transact-sql

Erik Shilts
  • 4,389
  • 2
  • 26
  • 51
  • 1
    FYI - this is in preview now. Soon you'll be able to use the JSON functions just as you currently do in SQL Server. https://azure.microsoft.com/en-us/blog/azure-sql-data-warehouse-releases-new-capabilities-for-performance-and-security/ – Gabe May 13 '19 at 19:06

1 Answers1

0

Update June 2019: This feature is in preview as at end of May 2019:

https://azure.microsoft.com/en-us/blog/azure-sql-data-warehouse-releases-new-capabilities-for-performance-and-security/

You should still consider if this is appropriate to do in your expensive Azure SQL Data Warehouse.

No. Do this kind of processing in Azure SQL Database which does actually support JSON processing. Start here:

Getting started with JSON features in Azure SQL Database

https://learn.microsoft.com/en-us/azure/sql-database/sql-database-json-features

You should also read this SQL Data Warehouse patterns and anti-patterns article. Consider if your expensive MPP is the right place for this type of processing: https://blogs.msdn.microsoft.com/sqlcat/2017/09/05/azure-sql-data-warehouse-workload-patterns-and-anti-patterns/

wBob
  • 13,710
  • 3
  • 20
  • 37
  • Unfortunately it's not possible to do in Azure SQL Database because the data come from an external data source. – Erik Shilts Nov 29 '17 at 04:30
  • Another option might be ADLA / USQL which supports JSON processing (with only a little customisation). Process it with USQL, pop it out into ADLS as a flat file, pick up in Warehouse with Polybase (if the volume genuinely justifies use if SQL DW). – wBob Nov 29 '17 at 08:25
  • 1
    @wbob I disagree Since Azure DW is so heavily dependant on ELT they should enhance it to support the JSON I could also see json being useful for storing logging information about the ELT process itself: https://feedback.azure.com/forums/307516-sql-data-warehouse/suggestions/13580676-add-support-of-json-format – Jason Horner Feb 03 '18 at 02:45