How to implement OPENJSON Having JSON code as a text inside a column. How do i use OPENJSON on the column of a table in AZURE SQL Dataware House?

Question

Eg: RAW DATA

| ORDER# | SUBORDER# |             DISCOUNTS          |
|------- |-----------| -------------------------------|
|   1    |    1-123  | '[{ discount:"1",amount:"1"}]' |
|   1    |    1-123  | '[{ discount:"2",amount:"2"}]' |

Want to apply OPENJSON on Discounts and have below OUTPUT:

| ORDER# | SUBORDER# | discount |   amount   |
|------- |-----------| ---------|------------|
|   1    |    1-123  |    1     |     1      |
|   1    |    1-123  |    2     |     2      |

Msg 137, Level 15, State 2, Line 44
Must declare the scalar variable "@discounts".

Hi @syed ahmed, please correct me if I understand you wrong in the answer. :) — Joseph Xu, Mar 03 '21 at 01:27

Joseph Xu · Accepted Answer · 2021-03-05T07:50:20.357

1

I've updated my answer, please add ; after the insert statement.

Create table and insert two rows:

create table dbo.test(

    ORDER# varchar(255),
    SUBORDER# varchar(255),
    DISCOUNTS varchar(255)
);

insert into dbo.test values ('1','1-123','[{ "discount":"1","amount":"1"}]');
insert into dbo.test values ('1','1-123','[{ "discount":"2","amount":"2"}]');

Then we can use following sql to query the data.

select ORDER#,SUBORDER#,A.*
from  dbo.test t
CROSS APPLY OPENJSON(t.DISCOUNTS) 
WITH (
    discount varchar(255),
    amount varchar(255)
) A;

3.The result is as follows:

edited Mar 05 '21 at 07:50

answered Mar 02 '21 at 08:50

Joseph Xu

5,607
2
5
15

Msg 319, Level 15, State 2, Line 14 Incorrect syntax near the keyword 'with'. If this statement is a common table expression, an xmlnamespaces clause or a change tracking context clause, the previous statement must be terminated with a semicolon. – syed ahmed Mar 05 '21 at 06:43
Hi, did you follow my steps? You can create a simple test. It works well. – Joseph Xu Mar 05 '21 at 06:51
Hi Joseph Xu, I follow you are steps same as well ,and I tried same as you are data aslo but I cant, if they have any other logic????? – syed ahmed Mar 05 '21 at 07:00
Did you add double quotes to `discount` and `amount`? – Joseph Xu Mar 05 '21 at 07:07
create table dbo.test( ORDER# varchar(255), SUBORDER# varchar(255), DISCOUNTS varchar(255) ) insert into dbo.test values ('1','1-123','[{ "discount":"1","amount":"1"}]'), ('1','1-123','[{ "discount":"2","amount":"2"}]') select ORDER#,SUBORDER#,A.* from dbo.test as t CROSS APPLY OPENJSON(t.DISCOUNTS) WITH ( discount varchar(255), amount varchar(255) ) A; select * from dbo.test – syed ahmed Mar 05 '21 at 07:25
Please add `;` after the insert statement. – Joseph Xu Mar 05 '21 at 07:49
Hi @syed ahmed , Do you have any questions? If my answer is helpful for you, please accept(mark) it as answer. Very Thanks! : ) – Joseph Xu Mar 06 '21 at 05:12

How to implement OPENJSON Having JSON code as a text inside a column. How do i use OPENJSON on the column of a table in AZURE SQL Dataware House?

1 Answers1