次の方法で共有

Encapsulate JSON parsing with inline table value functions

  • [アーティクル]

If you are storing JSON fields in SQL Server 2016 or Azure SQL Database, you would probably need to use OPENJSON to parse JSON and extract fields.

As an example, in new SQL Server 2016 WideWorldImporters sample database, we have Application.People table with several standard columns and one JSON column that contains custom fields.

If you want to query both standard column and JSON fields, you can write something like:

 select FullName, LogonName, EmailAddress, Title, CommissionRate
from Application.People
 cross apply OPENJSON(CustomFields)
             WITH(Title nvarchar(50), HireDate datetime2, OtherLanguages nvarchar(max) as json,
                  PrimarySalesTerritory nvarchar(50), CommissionRate float)

FullName, LogonName, and EmailAddress are coming from standard columns, while Title and CommissionRate are placed in JSON column CustomFields. In order to return JSON fields, we need to use CROSS APPLY OPENJSON on custom fields, and define schema of JSON that will be returned in WITH clause.

This schema-on-query definition might be handy if you need different JSON fields in different queries, but if you know that you will always use the same fields from JSON columns, you would need to copy-paste the same WITH clause in every query.

As an alternative, you can create inline table value function where you can encapsulate OPENJSON and WITH specification:

 go
drop function if exists Application.PeopleData
go
create function Application.PeopleData(@data nvarchar(max))
returns table
as return(select *
 from OPENJSON(@data)
      WITH(Title nvarchar(50), HireDate datetime2, 
           PrimarySalesTerritory nvarchar(50), CommissionRate float,
           OtherLanguages nvarchar(max) as json)
)
go

Now, the query can be simpler:

 select FullName, LogonName, EmailAddress, Title, CommissionRate
from Application.People
     cross apply Application.PeopleData(CustomFields)

We don't need to repeat WITH clause in every query that need to parse JSON fields. If you know what is JSON schema, you can encapsulate your parsing logic in one place and expose just keys that you need to use in queries.