CODEWITHSUNDEEP
sqljsont-sqlsql-server-2017
Casey Crookston
Casey Crookston

Reputation: 13945

Error when using CONVERT while SELECTing from OPENJSON

Given a variable @PartsData that is a VARCHAR(MAX) and contains a set of json data, this query works fine:

SELECT 
       *
FROM OPENJSON(@PartsData)
        WITH (
            PartId INT 'strict $.PartId', 
            ....
            CultureName VARCHAR(3) '$.CultureName',
            PartDescription VARCHAR(2000) '$.PartDescription'
        ) A

But, suppose I want to transform some of the data as I open it from the jason:

SELECT 
       *
FROM OPENJSON(@PartsData)
        WITH (
            PartId INT 'strict $.PartId', 
            ....
            CultureName VARCHAR(3) '$.CultureName',
            PartDescription VARCHAR(2000) '$.PartDescription'
            PhraseHash UNIQUEIDENTIFIER CONVERT([uniqueidentifier],hashbytes('MD2',concat('$.PartDescription', '$.CultureName'))
        ) A

On this, I get:

Incorrect syntax near the keyword 'CONVERT'.

This works okay, but I need the calculated value in the original WITH so I can join on it:

SELECT 
       *
       , CONVERT([uniqueidentifier],hashbytes('MD2',concat(CAST(A.PartDescription as NVARCHAR(max)),A.CultureName))) AS PhraseHash
FROM OPENJSON(@PartsData)
        WITH (
            PartId INT 'strict $.PartId', 
            ....
            CultureName VARCHAR(3) '$.CultureName',
            PartDescription VARCHAR(2000) '$.PartDescription'
        ) A

Upvotes: 2

Views: 1096

Answers (1)

Gottfried Lesigang
Gottfried Lesigang

Reputation: 67291

The WITH-clause is not the right place for this, but you can use a CROSS APPLY to compute the needed value row wise and use this value in a join. Something along this sample:

DECLARE  @tbl TABLE(ID INT,SomeValue VARCHAR(100));
INSERT INTO @tbl VALUES
 (1020,'Values 10 and 20')
,(2030,'Values 20 and 30');

DECLARE @json NVARCHAR(MAX)=
N'{
    "rows":
     [{"id":"1","val1":"10","val2":"20"},
      {"id":"2","val1":"20","val2":"30"},
      {"id":"3","val1":"20","val2":"30"},
      {"id":"4","val1":"40","val2":"30"}
    ]
  }';

  SELECT *
  FROM OPENJSON(@json,'$.rows')
  WITH(id INT,val1 NVARCHAR(10),val2 NVARCHAR(10)) A
  CROSS APPLY(SELECT CAST(CONCAT(A.val1,A.val2) AS INT)) B(ConcatenatedAndCasted)
  INNER JOIN @tbl t ON t.ID=B.ConcatenatedAndCasted;

For your next question: Please try to set up a MCVE (like I've done above) yourself. Add sample data and the expected output. Add your own attempts and explain, why your output is not fullfilling your needs. This will help much more than a thousand words...

UPDATE

You can retrieve a derived table from your JSON data too like here:

  WITH JSON_Data AS
  (
  SELECT *
  FROM OPENJSON(@json,'$.rows')
  WITH(id INT,val1 NVARCHAR(10),val2 NVARCHAR(10)) A
  )
  SELECT * FROM  @tbl t
  INNER JOIN JSON_Data jd ON t.ID=CAST(CONCAT(jd.val1,jd.val2) AS INT);

In fact there are various approaches using CTEs, sub-queries, applies, or even temp tables. Which one is the best for you, is a matter of row count and your needs...

Upvotes: 1

Related Questions