SQL Server Table to JSON

SQL Server table to json

I wouldn't really advise it, there are much better ways of doing this in the application layer, but the following avoids loops, and is a lot less verbose than your current method:

CREATE PROCEDURE dbo.GetJSON @ObjectName VARCHAR(255), @registries_per_request smallint = null
AS
BEGIN
    IF OBJECT_ID(@ObjectName) IS NULL
        BEGIN
            SELECT Json = '';
            RETURN
        END;

    DECLARE @Top NVARCHAR(20) = CASE WHEN @registries_per_request IS NOT NULL 
                                    THEN 'TOP (' + CAST(@registries_per_request AS NVARCHAR) + ') ' 
                                    ELSE '' 
                                END;

    DECLARE @SQL NVARCHAR(MAX) = N'SELECT ' + @Top + '* INTO ##T ' + 
                                'FROM ' + @ObjectName;

    EXECUTE SP_EXECUTESQL @SQL;

    DECLARE @X NVARCHAR(MAX) = '[' + (SELECT * FROM ##T FOR XML PATH('')) + ']';

    SELECT  @X = REPLACE(@X, '<' + Name + '>', 
                    CASE WHEN ROW_NUMBER() OVER(ORDER BY Column_ID) = 1 THEN '{'
                         ELSE '' END + Name + ':'),
            @X = REPLACE(@X, '</' + Name + '>', ','),
            @X = REPLACE(@X, ',{', '}, {'),
            @X = REPLACE(@X, ',]', '}]')
    FROM    sys.columns
    WHERE   [Object_ID] = OBJECT_ID(@ObjectName)
    ORDER BY Column_ID;

    DROP TABLE ##T;

    SELECT  Json = @X;

END

N.B. I've changed your two part object name (@schema and @table) to just accept the full object name.

Example on SQL Fiddle

The idea is to basically use the XML extension within SQL-Server to turn the table into XML, then just replace the start tags with {ColumnName: and the end tags with ,. It then requires two more replaces to stop add the closing bracket to the last column of each row, and the remove the final , from the JSON string.

SQL Server table data to JSON Path result

As I mention in the comments, I strongly recommend you fix your design and normalise your design. Don't store delimited data in your database; Re;BoRe;Va should be 3 rows, not 1 delimited one. That doesn't mean you can't achieve what you want with your denormalised data, just that your design is flawed, and thus it needs being brought up.

One way to achieve what you're after is with some nested FOR JSON calls:

SELECT (SELECT V.Column1 AS service
        FOR JSON PATH) AS services,
        (SELECT SS.[value] AS service 
         FROM STRING_SPLIT(V.Column2,';') SS
         FOR JSON PATH) AS additional_services
FROM (VALUES(1,'T1','Re;BoRe;Va'))V(ID,Column1,Column2)
FOR JSON PATH, WITHOUT_ARRAY_WRAPPER;

This results in the following JSON:

{
    "services": [
        {
            "service": "T1"
        }
    ],
    "additional_services": [
        {
            "service": "Re"
        },
        {
            "service": "BoRe"
        },
        {
            "service": "Va"
        }
    ]
}

How to get data from json column in SQL Server that starts with array element

SELECT Name FROM dbo.JData 
CROSS APPLY OPENJSON (JsonData)
WITH 
(
  Categories nvarchar(max) AS json,
  Id uniqueidentifier,
  [Name] varchar(10)
);

• Example db<>fiddle

SQL to JSON - Create a Json Array

You can use STRING_AGG to build the array like this:

SELECT  DISTINCT ID
      ,Product
      ,json_query(QUOTENAME(STRING_AGG('"' + STRING_ESCAPE(Usage, 'json') + '"', char(44)))) AS 'Usage'
FROM mytable T1
GROUP BY ID
      ,Product
FOR JSON PATH;

If you are not using SQL Sever 2017 or later, you can use concatenate the values using XML PATH.

SELECT DISTINCT T1.ID
      ,T1.Product
      ,
       (
         '[' +
          STUFF
          (
                (
                SELECT ',' + '"' + T2.Usage + '"' 
                FROM mytable T2
                WHERE T1.ID=T2.ID
                FOR XML PATH, TYPE
                ).value('.', 'NVARCHAR(MAX)')
                ,1
                ,1
                ,''
            )
          + ']'
       ) as 'Usage'
FROM mytable T1
FOR JSON PATH

---

For your edit use:

SELECT Distinct ID
      ,Product
      ,json_query('[' + (STRING_AGG('"' + STRING_ESCAPE(TRIM(Usage), 'json') + '"', char(44))) + ']') AS 'Usage'
FROM mytable
GROUP BY ID
      ,Product
FOR JSON PATH;

The issue is QUOTENAME input is limited to 128 chars and returns NULL when you add more records.

Unable to insert data from the JSON in to the SQL server table

Your primary issues is an extra comma in the SQL, which is a syntax error.

It's probably easier to just pass the whole JSON to SQL Server and shred it using OPENJSON

var result = await response.Content.ReadAsStringAsync();

ConnectionManager cm = Dts.Connections["SurplusMouse_ADONET"];

SqlConnection sqlConn = null;
try
{
    sqlConn = (SqlConnection)cm.AcquireConnection(Dts.Transaction));
    const string query = @"
INSERT INTO dbo.RM_Room
  (ROOMID, NAME, DESCRIPTION, SHIPPING_FREEZE, RECEIVING_FREEZE, MOUSE_NOROVIRUS, IRRADIATED_FEED)
SELECT
  id,
  name,
  description,
  CASE shippingFreeze WHEN 1 THEN 'T' ELSE 'F' END,
  CASE receivingFreeze WHEN 1 THEN 'T' ELSE 'F' END,
  CASE mouseNorovirus WHEN 1 THEN 'T' ELSE 'F' END,
  CASE irradiatedFeed WHEN 1 THEN 'T' ELSE 'F' END
FROM OPENJSON(@json)
  WITH (
    id int,
    name varchar(100),
    description varchar(1000),
    shippingFreeze bit,
    receivingFreeze bit,
    mouseNorovirus bit,
    irradiatedFeed bit
  ) j;
";
    using (var sqlCmd = new SqlCommand(query, sqlConn))
    {
        sqlCmd.Parameters.Add("@json", SqlDbType.NVarChar, -1).Value = result; 
        await sqlCmd.ExecuteNonQueryAsync();
    }
}
catch (Exception ex)
{
    Dts.TaskResult = (int)ScriptResults.Failure;
}
finally
{
    if(sqlConn != null)
        cm.ReleaseConnection(sqlConn);
}

Notes:

• sqlCmd.CommandType = CommandType.Text is unnecessary.
• ReleaseConnection needs to be in a finally
• Although it's unclear why you are using ConnectionManager in the first place. You should probably create the SqlConnection directly, and put it in a using
• Avoid AddWithValue, instead specify types and lengths explicitly.
• Use Async versions of code with await. Do not call .Result or you may deadlock.

Create JSON from SQL Server Table With Column Value as JSON Property Name

FOR JSON AUTO works from the column names, so one method to get your desired result would be to PIVOT the property names into columns. For example:

SELECT Color, [Name], Cost 
FROM dbo.Properties
PIVOT ( MAX( PropertyValue ) For PropertyName In ( [Color], [Name], Cost ) ) pvt
FOR JSON AUTO;

My results:

Of course this is only convenient if your JSON attributes / column names are always known and it's a simple example. For more complex examples, you are probably looking at dynamic pivot, or dynamic SQL and your STRING_AGG example isn't so bad.

Read Json Value from a SQL Server table

The JSON string is an array with a single item. You need to specify the array index to retrieve a specific item, eg :

declare @t table (json_val  nvarchar(4000))

insert into @t
values ('[{"prime":{"image":{"id":"123","logo":"","productId":"4000","enable":true},"accountid":"78","productId":"16","parentProductId":"","aprx":"4.599"}}]')

select JSON_VALUE(cast(json_val as varchar(8000)), '$[0].prime.aprx') as px
from @t

This returns 4.599

If you want to search all array entries, you'll have to use OPENJSON. If you need to do that though ...

Avoid JSON if possible

JSON storage is not an alternative to using a proper table design though. JSON fields can't be indexed, so filtering by a specific field will always result in a full table scan. Given how regular this JSON string is, you should consider using proper tables instead

---

Related Topics