2

I am looking to create well formatted JSON from a SQL Server stored procedure.

My problem is as follows:

Creating JSON like:

DECLARE @x varchar(20)
DECLARE @y varchar(20)

CREATE TABLE #temp
(
  JSON varchar(max)
)

INSERT INTO #temp
VALUES (
'{' +
    case when x IS NOT NULL BEGIN
      '"key1":"' + x + '"' +
    END
    case when y IS NOT NULL BEGIN
      '"key1":"' + y + '"' +
    END
 '}')

What is the best way to have "s" be well formatted json?? I would like to minimize the logic as much as possible (i.e. not have a branch for each of x*y combinations).

If a comma is put at the end of the x if conditions, it fails if y is null. If a comma is put before inserting y into the json, it fails if x is null.

Is there a better way to achieve this in SQL Server?

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
HedonicHedgehog
  • 592
  • 1
  • 6
  • 17
  • Can you post a meaningful SQL query. From where `X` values are coming from. Also which version of `SQL Server` you are using – Pரதீப் Feb 04 '16 at 04:16
  • x values are coming from another table, obviously they are nullable, but the values do not necessarily matter other than they are valid varchar(20)s. SQL Server independent? This is less of a specifics and more of a best practice question. Is there a better way to make JSON using SQL when dynamically pulling values from another table? – HedonicHedgehog Feb 04 '16 at 04:22
  • 2
    I wouldn't do this from SQL unless there was just no other option. You should query the results natively and then transform them to json in the consuming app. – Sam Axe Feb 04 '16 at 04:23
  • 1
    `SQL Server 2016` has a native support to `JSON`. https://msdn.microsoft.com/en-us/library/dn921897.aspx – Pரதீப் Feb 04 '16 at 04:25

1 Answers1

0

[edit based on OP's comment]

I have changed the way JSON is constructed and also generalized it to any number of key => value pairs:

drop table #tokens
GO

CREATE TABLE #tokens
(
    Key_ VARCHAR(32),
    Val VARCHAR(255)
)

INSERT INTO #tokens VALUES ('key1', 'val1'), ('key1', 'val2'), ('key3', NULL), ('key4', 'val3')
GO

DECLARE @JSON VARCHAR(MAX)

-- add key => pairs along with some newlines to have a decent output
SELECT
    @JSON = CASE
        WHEN @JSON IS NULL AND Key_ IS NOT NULL
        THEN '"' + Key_ + '":"' + ISNULL(Val, '') + '"'
        ELSE @JSON + ',' + CHAR(13) + CHAR(10) + '"' + Key_ + '":"' + ISNULL(Val, '') + '"'
    END
FROM
    #tokens AS T

SELECT '{' + CHAR(13) + CHAR(10) + @JSON + CHAR(13) + CHAR(10) + '}'

Of course, another option (which might be way easier for larger data hierarchies) is to serialize the objects in C# / Java / other high level language that supports OOP and JSON serialization.

Personally, I like structured data, because they allow database constraints like foreign key, unique, check constraints etc. In this case, I would advice to store data in normalized tables whenever possible and have the transformation in the application layer (C#, Java etc.).

Most of the languages allow to easily serialize/deserialize to/from JSON format, so that can brake down your data into normalized tables. This is particularly important when running some aggregations against your data, as it is much easier (and faster) to apply these functions on normalized data.

More details on this last topic can be found here.

Community
  • 1
  • 1
Alexei - check Codidact
  • 22,016
  • 16
  • 145
  • 164
  • So the problem with this answer is the same I am asking for help with unless I am mistaken: If x is null, then your final output will be: {"key1":"Y_VALUE",} JSON does not accept training commas so this results in invalid JSON – HedonicHedgehog Feb 04 '16 at 18:57
  • I agree on your points about moving this to a higher level language - it would be much easier. One possible tweak to this solution that uses a hacky `COALESCE` (which I only remembered because of running into a similar need long ago and a quick SO search turned up) is something like this: `select @JSON = COALESCE(@JSON + ', ', '') + '"' + Key_ + '": "' + ISNULL(Val, '') + '"' from #tokens`. I think that clears up any trailing comma issues. – Peter Tirrell Feb 04 '16 at 20:39