How to structure JSON SQL by row using values from the row?

Question

I'm struggling to structure JSON using SQL.

Say I have a table like this:

| col1          | col2     | col3     |
+---------------+----------+----------+
| specialvalue  | someval  | someval  |
| specialvalue2 | someval2 | someval2 |
|               |          |          |

I'm trying to get a structure like the following:

{
    "specialvalue": {
        "specialcol": "specialvalue",
        "col2": "someval",
        "col3": "someval"
    },
    "specialvalue2": {
        "specialcol": "specialvalue2",
        "col2": "someval2",
        "col3": "someval2"
    }
}

How can I accomplish this? Can I use JSON_MODIFY with dynamic keys, while mapping through every row in the set?

The closest I've gotten is the following:

SELECT
     specialcol,
     col2,
     col3
INTO #tmpTbl
FROM myTable

SELECT 
    specialcol, 
    (SELECT * FROM #tmpTbl FOR JSON AUTO) as 'Value' 
FROM #tmpTbl 
FOR JSON AUTO

DROP TABLE #tmp

Which returns the following:

{
    "specialcol":"specialvalue",
    "Value":{
        "col1": "specialvalue",
        "col2": "someval",
        "col3": "someval"
    },
    "specialcol":"specialvalue2",
    "Value":{
        "col1": "specialvalue2",
        "col2": "someval2",
        "col3": "someval2"
    }
}

Which is close, but not quite what I need.

Is there a way to use JSON_MODIFY to accomplish what I'm trying to get?

I don't believe so. You could get close with FOR JSON with some tweaks to what you already have but your top level "key" would end up named the same when you need it based on the value in col1. Not exactly the same, but similiar situation and they ended up not using FOR JSON stackoverflow.com/questions/46168258/… — Tim Mylott
– Tim Mylott, Commented Oct 24, 2018 at 19:08

Tim Mylott · Accepted Answer · 2018-10-24 19:33:05Z

1

From my comment and thinking about this. Not the most elegant, but could potential get you there.

Inline query to get the results in json, then wrap those results based on the value from your col1 as the key. Put it all together comma delimited and then a final wrap in brackets.

DECLARE @TestData TABLE
    (
        [col1] NVARCHAR(100)
      , [col2] NVARCHAR(100)
      , [col3] NVARCHAR(100)
    );

DECLARE @JsonValue NVARCHAR(MAX) = '';

INSERT INTO @TestData (
                          [col1]
                        , [col2]
                        , [col3]
                      )
VALUES ( 'specialvalue', 'someval', 'someval' )
     , ( 'specialvalue2', 'someval2', 'someval2' );


SELECT @JsonValue = @JsonValue + N'"' + [a].[col1] + N'": '
                    + (
                          SELECT [aa].[col1] AS 'specialvalue'
                               , [aa].[col2] AS 'col2'
                               , [aa].[col3] AS 'col3'
                          FROM   @TestData [aa]
                          WHERE  [aa].[col1] = [a].[col1]
                          FOR JSON PATH, WITHOUT_ARRAY_WRAPPER
                      ) + N','
FROM   @TestData [a];

SET @JsonValue = N'{' + SUBSTRING(@JsonValue, 1, LEN(@JsonValue) - 1) + N'}';
SELECT @JsonValue;

Gets you this:

{
  "specialvalue": {
    "specialvalue": "specialvalue",
    "col2": "someval",
    "col3": "someval"
  },
  "specialvalue2": {
    "specialvalue": "specialvalue2",
    "col2": "someval2",
    "col3": "someval2"
  }
}

answered Oct 24, 2018 at 19:33

Tim Mylott

2,8511 gold badge8 silver badges15 bronze badges

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

1 Comment

JD Francis Over a year ago

I like it. I mean sure its a bit hacky, but the other solutions I could think of were doing dynamic sql and iterating through temp tables, which is also hacky, but terrible performance. This is a simpler and more performant. Thanks!

Dhana · Accepted Answer · 2018-10-24 17:44:01Z

0

SQL Server 2017 -

;WITH cte as (
select 'specialvalue'  as specialcol,  'someval'  col2, 'someval'  col3 union 
select 'specialvalue2' as specialcol,  'someval2' col2, 'someval2' col3 )
SELECT 
CASE WHEN specialcol = 'specialvalue' THEN specialcol END as [specialvalue.specialcol],
CASE WHEN specialcol = 'specialvalue' THEN col2 END as [specialvalue.col2],
CASE WHEN specialcol = 'specialvalue' THEN col3 END as [specialvalue.col3],
CASE WHEN specialcol = 'specialvalue2' THEN specialcol END as [specialvalue2.specialcol],
CASE WHEN specialcol = 'specialvalue2' THEN col2 END as [specialvalue2.col2],
CASE WHEN specialcol = 'specialvalue2' THEN col3 END as [specialvalue2.col3]
FROM cte
FOR JSON PATH

Output:

{
"specialvalue":{
    "specialcol":"specialvalue",
    "col2":"someval",
    "col3":"someval"
   }
},
{
"specialvalue2":{
    "specialcol":"specialvalue2",
    "col2":"someval2",
    "col3":"someval2"
    }
}

answered Oct 24, 2018 at 17:44

Dhana

1,6584 gold badges24 silver badges40 bronze badges

1 Comment

JD Francis Over a year ago

Thats not a viable solution - you effectively just hardcoded the keys 'specialvalue' and 'specialvalue2' that I provided in my example. Those values could be anything, I cant use hardcoded values as keys.

Collectives™ on Stack Overflow

How to structure JSON SQL by row using values from the row?

2 Answers 2

1 Comment

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related