SQL transform JSON: select one field from json array objects

Question

I have table created with a script

CREATE TABLE [dbo].[Queries]
(
    [Id] [INT] NOT NULL,
    [Name] [NVARCHAR](MAX) NOT NULL,
    [Module] [NCHAR](64) NOT NULL,
    [Query] [NVARCHAR](MAX) NOT NULL,

    PRIMARY KEY (Id)
)
GO

where Query column is JSON string like:

[
  {
     "FieldName": "TargetFieldName",
     "Filters": [
       { /* Description Filter 1 */ },
       ...
       { /* Description Filter N */ }
    ]
  }
]

And I'd like to select from that table: Id, Name, and distinct list of column names as json array in THIRD column.

For example for row:

-------------------------------------------------------------------------------------------------
| Id | Name    | Query                                                                          |
-------------------------------------------------------------------------------------------------
| 7  | Query 7 |  [{"FieldName": "A", ... },{ "FieldName": "B" ...},{"FieldName": "B", ... }]   |
-------------------------------------------------------------------------------------------------

I'd like to get

| Id | Name    | DistinctFieldNames |
+----+---------+--------------------+
| 7  | Query 7 |  ["A","B"]         |

My questions are:

Is it possible at all transform json data to other format json data at SQL Server side?
How to write a SQL query to select one field from json array of objects?

Thom A · Accepted Answer · 2019-12-02 11:03:31Z

1

Seems like the easiest would be to use a subquery to read the JSON, and the STRING_AGGto create the new one:

SELECT V.ID,
       V.Name,
       '[' + STRING_AGG('"' + ca.FieldName + '"',',') WITHIN GROUP (ORDER BY ca.FieldName)  + ']'        
FROM (VALUES(7,'Query 7','[{"FieldName": "A"},{ "FieldName": "B"},{"FieldName": "B"}]'))V(ID,[Name], Query)
     CROSS APPLY (SELECT DISTINCT OJ.FieldName
                  FROM OPENJSON (V.Query)
                       WITH (FieldName char(1)) OJ) ca --May  need larger size, char(1) based on your sample data
GROUP BY V.ID,
         V.[Name];

answered Dec 2, 2019 at 11:03

Thom A♦

97.7k12 gold badges67 silver badges102 bronze badges

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

Comments

Iman Kazemi · Accepted Answer · 2019-12-02 12:16:16Z

1

You should know that this code only run on SQL Server 2017 and higher version.

SELECT Id,Name,
    (SELECT STRING_AGG(JSON_VALUE(value, '$.FieldName'), ',') As FieldName 
     FROM OPENJSON(Queries.Query)) 
FROM dbo.Queries

edited Dec 2, 2019 at 12:16

answered Dec 2, 2019 at 11:56

Iman Kazemi

5525 silver badges17 bronze badges

Collectives™ on Stack Overflow

SQL transform JSON: select one field from json array objects

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related