Fetch data from Json array in rows and columns

Question

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

For SQL server, you can use OPENJSON to parse JSON to table - see learn.microsoft.com/en-us/sql/relational-databases/json/… — caaax
– caaax, Commented Feb 19, 2020 at 10:53

Darkwing · Accepted Answer · 2020-02-19 11:47:20Z

3

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

answered Feb 19, 2020 at 11:47

Darkwing

7,6857 gold badges37 silver badges66 bronze badges

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

Comments

Dipto Roy · Accepted Answer · 2020-02-19 11:59:23Z

0

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.

answered Feb 19, 2020 at 11:59

Dipto Roy

1631 silver badge15 bronze badges

Collectives™ on Stack Overflow

Fetch data from Json array in rows and columns

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