Manually writing Json into SQL column using SQL Functions

Question

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.

Slayton · Accepted Answer · 2020-08-09 03:17:31Z

1

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'
  );

edited Aug 9, 2020 at 3:17

answered Jul 10, 2020 at 15:19

Slayton

493 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

TeilaRei Over a year ago

Slayton, thanks for contributing. If you could edit your answer to include an actual explicit solution or reproducible example that would be great, and in line with community requirements. Link is good, but as a support to your solution.

Collectives™ on Stack Overflow

Manually writing Json into SQL column using SQL Functions

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related