SQL Server - Использование JSON для возврата имен столбцов

У меня есть следующий тестовый запрос, который мне нужно сделать динамическим.

В основном хранимая процедура будет передана @json, и ей необходимо вернуть имена столбцов, которые переданы в этой переменной.

Возможно ли это и как я могу это сделать?

declare @json varchar(max)

set @json = '["FirstName", "LastName","DOB"]';  

select *   
from OPENJSON( @json )  

select
FirstName,
LastName,
DOB
from Client

У меня есть это, что работает, но я не уверен, хороший ли это вариант и есть ли лучший способ

declare @json varchar(max)
declare @columnames varchar (200)
declare @sqlquery nvarchar(200)

set @json = '["FirstName", "LastName","DOB"]';  
set @columnames =''

select @columnames = 
    case when @columnames = ''
    then value
    else @columnames + coalesce(',' + value, '')
    end
  from OPENJSON( @json )  

set  @sqlquery = 'select ' + @columnames + ' from Client'

EXEC SP_EXECUTESQL @sqlquery

Обычно переменная @json может содержать одно, несколько или все поля ниже, а со временем и большее количество.

set @json = '["FirstName", "LastName","DOB","DrugName,"Age","AgeGroup","Overdose","VerificationCode","Gender"]';

json sql-server tsql sql-server-2016
person Philip    schedule 02.01.2017    source источник
comment
Лучший способ - это анализировать данные в приложении. Лучшая практика на sqlserver - создать функцию CLR TVL с десериализацией - эта функция недетерминирована, и вы должны использовать общий код в своем приложении. В этом примере строки json вы можете использовать работу со строками, но вы можете использовать гораздо более сложные строки, включая экранирующие символы. Вывод - парсим данные в app. Второй вариант - использовать сериализацию со скалярной CLR в xml и использовать xquery на sqlserver. Пример: sqlservercentral.com/articles/SQLCLR/74160.   -  person Deadsheep39    schedule 02.01.2017
comment
Вау, там есть какие-то сложные штуки @ Deadsheep39. Причина, по которой мне нужно это сделать, заключается в том, что я создаю настраиваемый отчет, в котором пользователю на интерфейсе требуется возможность выбрать столбцы, которые они хотят вернуть в отчет.   -  person Philip    schedule 02.01.2017
comment
Я не знаю, что это должно быть так сложно, как предполагает @ Deadshepp39. Конечно, я бы также посоветовал провести синтаксический анализ со стороны приложения. То есть вернуть все столбцы и решить, что показывать на стороне приложения. С динамическим SQL (в частности, sql-инъекцией) больше трафика данных, но меньше ошибок. Имеющийся у вас динамический SQL - единственное решение для этого, но вы правы, когда относитесь к нему с подозрением. Лучше избегать. SQL Server работает лучше всего, когда он точно знает, какие столбцы возвращаются все время.   -  person Nick.McDermaid    schedule 02.01.2017
comment
Спасибо за ваши мысли @ Nick.McDermaid Когда вы говорите, что передано на стороне приложения, что вы имеете в виду, поскольку я не уверен, что будет передано в хранимую процедуру?   -  person Philip    schedule 02.01.2017
comment
На основе вашего примера: ваше приложение запускает SELECT field1,field2,field3 FROM Table (т.е. каждое поле). Затем внутри приложения он решает (на основе вашего выбора пользователя), что на самом деле показано. Вот что я имею в виду под пар s ed. Возможно, это нечто большее, чем вы объяснили.   -  person Nick.McDermaid    schedule 02.01.2017
comment
Я понимаю, что вы имеете в виду @ Nick.McDermaid. Проблема в том, как вы думали, это немного сложнее, когда пользователь должен иметь возможность выбирать, следует ли группировать и подсчитывать / суммировать / усреднять по любому из полей. В целом это становится довольно запутанным сложным динамическим набором SQL, но я действительно застрял в том, как подойти к нему хорошо.   -  person Philip    schedule 02.01.2017
comment
Динамический SQL всегда сложен. У него также нет преимуществ в производительности по сравнению с кешированным планом. Другой способ - попробовать создать его в LINQ (не могу поверить, что предлагал это). Может быть, это интересно: weblogs.asp.net/scottgu/ Таким образом, вы можете включить свою сложную логику в свое приложение, и настоящий SQL-запрос будет отправлен в базу данных.   -  person Nick.McDermaid    schedule 02.01.2017
comment
Я нашел конвертер json в xml sqlsunday.com/2013/05/12/, который может вам помочь (но лучший (= гораздо более быстрый) способ - это функция clr). Вы можете преобразовать строку json в xml, а затем использовать xquery для получения данных. Если вы можете больше описания, добавьте, пожалуйста, больше реальных примеров строк, и я создам более длинный ответ с руководством.   -  person Deadsheep39    schedule 02.01.2017
comment
Спасибо за ваш постоянный вклад @ Nick.McDermaid. Ценить это. Ха-ха, LINQ. Я разделяю аналогичное мнение :) По крайней мере, я не пропустил ничего очевидного, поэтому спасибо   -  person Philip    schedule 02.01.2017
comment
Большое спасибо @ Deadsheep39. Я только что отредактировал основной пост со всеми возможными значениями переменных json.   -  person Philip    schedule 02.01.2017
comment
Всегда только заголовок без данных? Настоящая строка json может иметь вид {Person: {firstName: John, lastName: Smith, age: [25, 26, 27] ...}}   -  person Deadsheep39    schedule 02.01.2017
comment
Только что проверил @ Deadsheep39, и на самом деле он передает следующее: [\ FirstName \, \ LastName \, \ GenderID \]   -  person Philip    schedule 02.01.2017

Ответы (1)


arrow_upward
0
arrow_downward

В этом случае (ваша строка json содержит только заголовок с именами столбцов) и используется только для выбора столбца, чтобы это было просто. Это намного сложнее, если вы хотите также получать значения из строки json.

Вариант 1. Используйте системную таблицу для проверки введенных данных и получения имен столбцов.

declare @json varchar(800) = '["FirstName", "LastName", "DOB","DrugName,"Age","AgeGroup", "Overdose","VerificationCode","Gender"]',
@columnames varchar(800)

select @columnames = isnull(@sql + ', ', name) + name
from (
    select name, '"' + name + '"' name_quoted
    from sys.columns c
    where object_id('dbo.Client') = object_id) t
where @json like '%' + name_quoted + '%'

print @columnames
exec ('select ' + @columnames + ' from dbo.Client')

Вариант 2: Работа со строками.

declare @json varchar(800) = '["FirstName", "LastName","DOB","DrugName,"Age","AgeGroup","Overdose","VerificationCode","Gender"]';
declare @sql varchar(max) = replace(substring(@json, 2, len(@json) - 2), '"','')
print @sql
exec ('select ' + @sql + ' from dbo.Client')
person Deadsheep39    schedule 02.01.2017