CODEWITHSUNDEEP
sqljsonsql-server
userone
userone

Reputation: 321

Manually writing Json into SQL column using SQL Functions

Hello I need to write a JSON into a SQL column during a insert in a stored procedure.

Something like:

INSERT INTO TableA (ColumnA)
SELECT
  '{"PropertyA":' + '"' + valueA + '"' + ',"PropertyB":' + '"' + valueB + '"}
FROM TableB

The above works, and it inserts the following: {"PropertyA:""SomeValue","PropertyB:""AnotherValue"}

But I'm hoping to format the statement into something more legible using SQL Functions if they exist.

Upvotes: 0

Views: 35

Answers (1)

Slayton
Slayton

Reputation: 49

Sounds like you are looking for FROM OPENJSON(<jsonstring>)

See this example from the documentation


DECLARE @json NVARCHAR(MAX);
SET @json = N'[
  {"id": 2, "info": {"name": "John", "surname": "Smith"}, "age": 25},
  {"id": 5, "info": {"name": "Jane", "surname": "Smith"}, "dob": "2005-11-04T12:00:00"}
]';

SELECT *
FROM OPENJSON(@json)
  WITH (
    id INT 'strict $.id',
    firstName NVARCHAR(50) '$.info.name',
    lastName NVARCHAR(50) '$.info.surname',
    age INT,
    dateOfBirth DATETIME2 '$.dob'
  );

Upvotes: 1

Related Questions