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

Turn a table of JSON paths and values into a JSON object

I’ve got a table in my database that contains a bunch of JSON paths and the values at those paths. I want to take the data in that table and construct a JSON object out of it. I’m more of a .NET guy and my go to approach with that kind of background would just be to run a WHILE loop and call JSON_MODIFY() for each row in the table, but I’m posting here in hopes that someone has a more elegant/efficient solution that’d be more appropriate for SQL. It seems like OPENJSON() might be able to help me out here, but I’m not quite sure how to use it to here.

Table

CREATE TABLE [dbo].[JsonData]
(
    [Id] INT NOT NULL PRIMARY KEY IDENTITY,
    [Path] VARCHAR(MAX) NOT NULL,
    [Value] VARCHAR(MAX) NOT NULL
)

Not sure if the empty object value with Id #1 and #6 are necessary for all solutions to this. But with the WHILE loop at least it ensured we got the base object created so that JSON_MODIFY() could set the properties on it.

Sample Data

INSERT INTO [dbo].[JsonData] ([Path], [Value])
VALUES
('FullName', '{}'),
('FullName.FirstName', 'Robert'),
('FullName.MiddleName', 'John'),
('FullName.LastName', 'Downey'),
('FullName.Suffix', 'Jr.'),
('CurrentDate', '{}'),
('CurrentDate.Month', 'February'),
('CurrentDate.Day', '22'),
('CurrentDate.Year', '2024')

Desired Outcome

{
  "FullName": {
    "FirstName": "Robert",
    "MiddleName": "John",
    "LastName": "Downey",
    "Suffix": "Jr."
  },
  "CurrentDate": {
    "Month": "February",
    "Day": 22,
    "Year": 2024
  }
}

I appreciate any advice!

My Rudimentary WHILE loop solution

DECLARE @json VARCHAR(MAX) = '{}'
DECLARE @currentId INT
DECLARE @finalId INT

SELECT
    @currentId = MIN(Id),
    @finalId = MAX(Id)
FROM
    JsonData

WHILE @currentId <= @finalId
BEGIN
    DECLARE @path VARCHAR(MAX)
    DECLARE @value VARCHAR(MAX)
    SELECT
        @path = j.[Path],
        @value = j.[Value]
    FROM
        JsonData j
    WHERE
        j.Id = @currentId

    DECLARE @fullPath varchar(max) = FORMATMESSAGE('$.%s', @path)
    IF ISJSON(@value) = 1 -- Not ideal, but took a hacky route here to make this work as an example
        SET @json = JSON_MODIFY(@json, @fullPath, JSON_QUERY(@value))
    ELSE IF TRY_CONVERT(INT, @value) IS NOT NULL
        SET @json = JSON_MODIFY(@json, @fullPath, CAST(@value as INT))
    ELSE
        SET @json = JSON_MODIFY(@json, @fullPath, @value)

    SET @currentId = @currentId + 1
END

SQL Version
Microsoft SQL Azure (RTM) – 12.0.2000.8 Dec 6 2023 08:32:07 Copyright (C) 2022 Microsoft Corporation

Edit
As Dale mentioned, here’s the business requirements

  1. Should be able to handle any valid JSON path (i.e. anything that could be passed into JSON_MODIFY). We have flexibility on how to format the incoming paths if that is helpful somehow
  2. Should handle duplicate paths in the table and insert the latest data (higher ID = newer data) into the final JSON object
  3. Data should be inserted into the JSON object in its ‘native’ form. I’m sure there’s a better way to phrase this, but as an example I mean that if ’12’ is in the Value column, it should go into the JSON as 12 and not ’12’. We can add a column for data type to the JsonData table and populate it with the correct data type if that’s helpful. I edited in a rudimentary way to do this in my example SQL although I expect that wouldn’t be the best long term solution
  4. Should be able to handle a decent amount of data. In our use case, I think we’re expecting this table to have between 100 and 500 rows of applicable data, with the usual number being ~200. I think this might rule out the recursive approach

Sorry for not clarifying on all of these sooner

Tags:

2

Answers


  1. 0

    Here is one option using Dynamic SQL

    Declare @SQL nvarchar(max);

Select @SQL = '
Select
  ' + string_agg('  ' + quotename(Path) + ' = '' ' + string_escape(value, 'json') + '''', ',') + '
For JSON Path, Without_Array_Wrapper
'
 From YourTable
 Where Value <> '{}' ;

exec sp_executesql @sql;

    Results

    {
    "FullName": {
        "FirstName": "Robert",
        "MiddleName": "John",
        "LastName": "Downey",
        "Suffix": "Jr."
    },
    "CurrentDate": {
        "Month": "February",
        "Day": "22",
        "Year": "2024"
    }
}

    Update. Just noticed the Latest Path requirement

    Declare @SQL varchar(max) = (
Select 'Select '+string_agg(quotename(Path)+'='''+string_escape(value,'json')+'''',',') + ' For JSON Path, Without_Array_Wrapper '
 From (Select top 1 with ties *
        From YourTable
        Order By row_number() over (partition by path order by id desc)
      ) A
 Where Value<>'{}' 
)
Exec(@SQL)
    Login or Signup to reply.
  2. 0

    You could use the following recursive CTE.

    • Start with an empty object, and an Id lower than the lowest possible.
    • Recurse the CTE and OUTER APPLY the following:
      • Look for all rows in the table greater than the current ID.
      • Assign a row-number, and filter that to 1 to get the first row.
      • You’ll be pleased to know the compiler turns this into a TOP (1) automatically.
        We can’t do this as it’s not allowed in a rCTE.
    • Check the original ID is not null, otherwise we’ll recurse forever.
    • Select out of the CTE, filtering only to the one possible row that has a null ID: the last one.
    • We can use ISJSON to check for those {} values. They aren’t strictly necessary if you have child properties of those, but you may need them if you don’t have those.
    WITH cte AS (
    SELECT CAST(N'{}' AS nvarchar(max)) AS json, 0 AS Id

    UNION ALL

    SELECT
      IIF(jd.Path IS NULL,
        cte.json,
        IIF(ISJSON(jd.Value) = 1,
          JSON_MODIFY(cte.json, '$.' + jd.Path, JSON_QUERY(jd.value)),
          JSON_MODIFY(cte.json, '$.' + jd.Path, jd.Value)
        )
      ),
      jd.Id
    FROM cte
    OUTER APPLY (
        SELECT *
        FROM (
            SELECT *, ROW_NUMBER() OVER (ORDER BY jd.Id) AS rn
            FROM JsonData jd
            WHERE jd.Id > cte.Id
        ) jd
        WHERE jd.rn = 1
    ) jd
    WHERE cte.Id IS NOT NULL
)
SELECT cte.json
FROM cte
WHERE cte.Id IS NULL;

    db<>fiddle

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

Back To Top
Search