Home > Back-end >  Get data from column with JSON object
Get data from column with JSON object

Time:03-21

I have a SQL-server, in one table some data are stored as a JSON object.

ID Date Customer details
1 2022-01-01 {'Country':167,'AccountNumber':'123456','SwiftBic':'ABC123'}
2 2022-01-01 {'Country':666,'AccountNumber':'765432','SwiftBic':'XYZ123'}

How do "split" this info into separate columns like this: Result

ID Date Country AccountNumber SwiftBic
1 2022-01-01 167 123456 ABC123
2 2022-01-01 666 765432 XYZ123

CodePudding user response:

You can use JSON_VALUE for this :

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

If you need to use these columns frequently, especially if you need to use them in WHERE clauses, you can create computed columns using JSON_VALUE and index them:

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

CREATE INDEX IX_ThatTable_Country ON dbo.ThatTable(Country);

It's not possible to expand arbitrary properties to columns. The columns in a query are similar to types in a strongly typed language and have to be known in advance.

Single or Double quotes?

The JSON Standard specifies that only double-quotes can be used as quotation marks in JSON.

  quotation-mark = %x22      ; "

Parsers and serializers must use and understand " as a quotation mark but are free to reject '. Using ' can lead to problems.

T-SQL is strict about this, so the following is invalid :

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

While this is valid:

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

CodePudding user response:

Another option, which is easier if you have many properties to pull out, is to use 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;

db<>fiddle

As mentioned, single-quotes are not valid as JSON delimiters, so you need to fix that up.

  • Related