Получить данные из столбца с объектом JSON

У меня есть SQL-сервер, в одной таблице некоторые данные хранятся в виде JSON-объекта.

Я БЫ	Дата	Детали клиента
1	2022-01-01	{'Страна':167,'Номер счета':'123456','SwiftBic':'ABC123'}
2	2022-01-01	{'Страна':666,'Номер счета':'765432','SwiftBic':'XYZ123'}

Как «разделить» эту информацию на отдельные столбцы, например: Результат

Я БЫ	Дата	Страна	Номер счета	Swift BIC
1	2022-01-01	167	123456	ABC123
2	2022-01-01	666	765432	XYZ123

Какую систему баз данных вы используете? Это Microsoft SQL Server или что-то еще?

— 21.03.2022 11:45

Да, не уверен насчет версии, но я думаю, что это 2019

— 21.03.2022 12:36

Вы можете использовать JSON_VALUE для этого

— 21.03.2022 12:43

sql json sql-server

21.03.2022 11:37

ReactJs | Supabase | Добавление данных в базу данных

Это и есть ваш редактор таблиц в supabase.👇

Понимание Python и переход к SQL

Перед нами лабораторная работа по BloodOath:

Перейти к ответу Данный вопрос помечен как решенный

Ответы 2

Ответ принят как подходящий

Вы можете использовать JSON_VALUE для этого:

SELECT 
    ID, 
    Details, 
    JSON_VALUE(details,'$.Country') AS Country,
    JSON_VALUE(details,'$.AccountNumber') AS AccountNumber,
    JSON_VALUE(details,'$.SwiftBic') AS SwiftBic
FROM ThatTable

Если вам нужно часто использовать эти столбцы, особенно если вам нужно использовать их в предложениях WHERE, вы можете создать вычисляемые столбцы с помощью JSON_VALUE и проиндексировать их:

CREATE TABLE ThatTable (
   ...
   Country AS JSON_VALUE(CustomerDetails, '$.Country') PERSISTED,
   ...
)

CREATE INDEX IX_ThatTable_Country ON dbo.ThatTable(Country);

Невозможно расширить произвольные свойства до столбцов. Столбцы в запросе аналогичны типам в строго типизированном языке и должны быть известны заранее.

Одинарные или двойные кавычки?

Стандарт JSON указывает, что в качестве кавычек в JSON можно использовать только двойные кавычки.

  quotation-mark = %x22      ; "

Парсеры и сериализаторы должен используют и понимают " как кавычки, но могут отклонять '. Использование ' может привести к проблемам.

T-SQL строго относится к этому, поэтому следующее недопустимо:

{'Country':167,'AccountNumber':'123456','SwiftBic':'ABC123'}

Пока это действительно:

{"Country":167,"AccountNumber":"123456","SwiftBic":"ABC123"}

Спасибо, я попробовал это, но получил код ошибки: текст JSON неправильно отформатирован. Неожиданный символ ''' найден в позиции 1. Мне кажется, что это JSON, но я думаю, что это не так?

— 21.03.2022 12:53

@EmeliaJohansson В стандарте JSON для разделения текста можно использовать только двойные кавычки. SQL Server строго следует этому. Вам нужно будет использовать REPLACE(field,'''','"'), чтобы заменить одинарные двойные кавычки

— 21.03.2022 13:03

21.03.2022 12:45

Другой вариант, который проще, если у вас есть много свойств для извлечения, — это использовать OPENJSON

SELECT 
    t.ID,
    t.Date,
    j.*
FROM YourTable t
OUTER APPLY OPENJSON(t.details)
  WITH (
    Country varchar(30),
    AccountNumber varchar(10),
    SwiftBic varchar(30)
  ) j;

дб <> рабочий пример

Как уже упоминалось, одинарные кавычки недействительны в качестве разделителей JSON, поэтому вам нужно это исправить.

21.03.2022 13:13