Insert data from Json that has multiple rows into a table in SQL

Question

Is there any way to insert data from Json which data stored in several rows, insert into a regular table at first I try to use FOR JSON AUTO but it returns NULL

SELECT *
FROM OPENJSON((SELECT * FROM JsonOneColumn))

Json table is like this

| Value                                                           | 
| --------------------------------------------------------------- | 
| {"Fname":"Cake","Fcount":3,"FDate":"2020-02-13","Fregion":"UK"} | 
| {"Fname":"Coca","Fcount":5,"FDate":"2020-02-13","Fregion":"US"} | 
...

it should be like this:

| Fname | Fcount | FDate    | Fregion |  
| ----- | ------ | -------- | ------- |
| Cake  | 3      |2020-02-13| UK      |
| Coca  | 5      |2020-02-13| US      |

Is JsonOneColumn the name of the table where the json data are being stored? — Giorgos Betsos
– Giorgos Betsos, Commented Jan 10, 2021 at 7:28

Giorgos Betsos · Accepted Answer · 2021-01-10 15:36:44Z

1

Assuming your JSON data is stored in a way similar to the following:

CREATE TABLE Logs (
    _id bigint primary key identity,
    log nvarchar(max)
);

ALTER TABLE Logs
    ADD CONSTRAINT [Log record should be formatted as JSON]
                   CHECK (ISJSON(log)=1)
                   
INSERT INTO Logs
VALUES
('{"Fname":"Cake","Fcount":3,"FDate":"2020-02-13","Fregion":"UK"}'),
('{"Fname":"Coca","Fcount":5,"FDate":"2020-02-13","Fregion":"US"}')

you can query the data like this:

SELECT 
  JSON_VALUE(log, '$.Fname') AS Fname
  , CAST(JSON_VALUE(log, '$.Fcount') AS INT) AS Fcount
  , CAST(JSON_VALUE(log, '$.FDate') AS DATETIME) AS FDate
  , JSON_VALUE(log, '$.Fregion') AS Fregion
FROM Logs

Output:

Fname	Fcount	FDate	Fregion
Cake	3	2020-02-13T00:00:00Z	UK
Coca	5	2020-02-13T00:00:00Z	US

Demo here

edited Jan 10, 2021 at 15:36

answered Jan 10, 2021 at 8:12

Giorgos Betsos

72.3k10 gold badges69 silver badges103 bronze badges

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

Comments

Sara Moradi · Accepted Answer · 2021-01-10 08:23:16Z

0

I think it will be my answer:

CREATE VIEW OneColumnIntoView
AS  
SELECT *
FROM OPENJSON((SELECT CONCAT('[',(STRING_AGG(VALUE,',')),']') AS JC FROM OneColumnJsonFood))

WITH (Fname NVARCHAR(50),
Fcount INT,
Datetype NVARCHAR(4000),
Fregion NVARCHAR(50)) AS WithJson

answered Jan 10, 2021 at 8:23

Sara Moradi

336 bronze badges

1 Comment

Amirhossein Over a year ago

Hi @sara moradi : this post isn't an answer, you should add this answer in your question for your tried. I voted your question for give good feedback but you should read how to answer questions in help center. (delete this answer)

Amirhossein · Accepted Answer · 2021-01-10 09:09:01Z

Try this one :

you need use PK table and here this name is @id you can choose another. i don't know about your data types


---"Fname":"Cake","Fcount":3,"FDate":"2020-02-13","Fregion":"UK"

SET NOCOUNT ON;  
  
DECLARE @id INT
Declare @table table (Fname nvarchar(50), Fcount int,FDate date,Fregion varchar(10))  
  
DECLARE CR CURSOR FOR   
SELECT [Value] FROM JsonOneColumn 
  
OPEN CR  
  
FETCH NEXT FROM CR   
INTO @id
  
WHILE @@FETCH_STATUS = 0  
BEGIN  

    Declare @test nvarchar(500) = (SELECT [Value] FROM JsonOneColumn where id = @id)
    
    Insert into @table (Fname, Fcount,FDate,Fregion)
    Select Fname, Fcount,FDate,Fregion
    From OpenJSON (@test) With ( Fname   nvarchar(50)   '$.Fname'
                                ,Fcount  int            '$.Fcount'
                                ,FDate   date           '$.FDate'
                                ,Fregion varchar(10))   '$.Fregion')
  
 
        -- Get the next id.  
    FETCH NEXT FROM CR   
    INTO @id  
END   
CLOSE CR;  
DEALLOCATE CR;  

-- Just for check out put
Select * From @table

Collectives™ on Stack Overflow

Insert data from Json that has multiple rows into a table in SQL

3 Answers 3

Comments

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related