Question 175074 in Json Question posted in
Our archive of expertly curated questions and answers provides insights and solutions to common problems related to this popular data interchange format. From parsing and manipulating JSON data to integrating it with various programming languages and web services, our archive has got you covered. Start exploring today and take your JSON skills to the next level

How can I rewrite this?

Trying to figure out how to convert multiple rows of data to a single row of KeyValue array
Setup:
using MS SQL SERVER 2017

raw data:

Select s.id, s.FkId, s.name ,s.value from tbl s where FkId = 1024180

id    | FkId    | name  | value
83713 | 1024180 | Name1 | Value1
83714 | 1024180 | Name2 | Value2

Desired result:

{ "Name1":"Value1", "Name2":"Value2" }

Failures/Learnings

I have tried different combos using Json Path and JSON_Array.. neither are resolving to what I need.

Select JSON_ARRAY(s.name,s.value) as result from tbl s where FkId = 1024180 

result is two rows with wrappers

row 0 ["Name1","Value1"],
row 1 ["Name2","Value2"]

tried: 
Select  s.name ,s.value from tbl s where fkid= 1024180 for json path
result:      
[{"name":"SpaceSubType","value":"Fee Earner"},{"name":"Department","value":"Property"}]

Is there a reasonable way to get the output I am trying for?

Tags:

2

Answers


  1. 0

    Please try the following solution.

    It will work starting from SQL Server 2017 onwards.

    SQL

    -- DDL and sample data population, start
DECLARE @tbl TABLE (id INT PRIMARY KEY, FkId INT, name VARCHAR(20), value VARCHAR(20));
INSERT @tbl (id, FkId, name, value) VALUES
(83713, '1024180', 'Name1', 'Value1'),
(83714, '1024180', 'Name2', 'Value2');
-- DDL and sample data population, end

SELECT '{' + STRING_AGG('"' + name + '": "' + value + '"', ',') + '}'
FROM @tbl
WHERE fkid = 1024180

    Output

    {
    "Name1": "Value1",
    "Name2": "Value2"
}
    Login or Signup to reply.
  2. 0

    Yet another option with pivot and FOR JSON PATH:

    SELECT MAX(CASE WHEN name = 'Name1' THEN value END) AS Name1,
       MAX(CASE WHEN name = 'Name2' THEN value END) AS Name2
FROM tab
FOR JSON PATH, WITHOUT_ARRAY_WRAPPER

    Output:

    Output
    {"Name1":"Value1","Name2":"Value2"}

    Check the demo here.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search