CODEWITHSUNDEEP
sqljsonsql-server
Red Devil
Red Devil

Reputation: 2393

Fetch data from Json array in rows and columns

I have this query to append the arrary in JSON:

DECLARE @TheTable table(TheJSON nvarchar(max), Condition int )
DECLARE @mystring nvarchar(100)='{"id": 3, "name": "Three"}'

INSERT INTO @TheTable SELECT '[{"id": 1, "name": "One"}, {"id": 2, "name": "Two"}]', 1

UPDATE @TheTable 
SET TheJSON = JSON_MODIFY(TheJSON, 'append $', JSON_QUERY(N'{"id": 3, "name": "Three"}')) 
WHERE Condition = 1;

SELECT TheJSON FROM @TheTable

It gives me the output in 1 single Row:

[{"id": 1, "name": "One"}, {"id": 2, "name": "Two"},{"id": 3, "name": "Three"}]

Till this part it is correct.

Now I want to fetch the data from this table in rows and columns wise like a regular table:

ID  Name    
1   One 
2   Two 
3   Three

How to fetch the data from Json in rows and columns wise?

Any help would be appreciated

Upvotes: 0

Views: 273

Answers (2)

Dipto Roy
Dipto Roy

Reputation: 163

DECLARE @mystring nvarchar(100)='{"id": 3, "name": "Three"}'

INSERT INTO @TheTable SELECT '[{"id": 1, "name": "One"}, {"id": 2, "name": "Two"}]', 1

UPDATE @TheTable 
SET TheJSON = JSON_MODIFY(TheJSON, 'append $', JSON_QUERY(N'{"id": 3, "name": "Three"}')) 
WHERE Condition = 1;

Declare @temp nvarchar(max)

select @temp= TheJSON   FROM @TheTable     

select id,name   FROM OPENJSON(@temp)                              
 WITH (                              
 id  NVARCHAR(100)  ,                            
 name   NVARCHAR(100)                                                    

 )

Hi you can try this. This is giving what you want . Thank you.

Upvotes: 0

Matt Evans
Matt Evans

Reputation: 7575

DECLARE @TheTable table(TheJSON nvarchar(max), Condition int )
DECLARE @mystring nvarchar(100)='{"id": 3, "name": "Three"}'

INSERT INTO @TheTable SELECT '[{"id": 1, "name": "One"}, {"id": 2, "name": "Two"}]', 1

UPDATE @TheTable 
SET TheJSON = JSON_MODIFY(TheJSON, 'append $', JSON_QUERY(N'{"id": 3, "name": "Three"}')) 
WHERE Condition = 1;

SELECT @mystring =  TheJSON FROM @TheTable

SELECT  p.*
    FROM 
    OPENJSON (@mystring, '$') WITH(id varchar(10) '$.id', name varchar (30) '$.name') p

Upvotes: 3

Related Questions