How to split JSON file into multiple rows in SQL

Question

Suppose now i have some table

studentId studentScore ScoreDetail     StudentName Phone  classId Subject     
1          50          (a json value)  Doe         911    01      math

the json file is like

{
  "DetailList": [
    {
      "source": "homework",
      "count": 30,
      "user_id": 1,
      "order_id": ": 0
    },
    {
      "source": "homework",
      "count": 20,
      "user_id": 1,
      "order_id": ": 0"
    }
  ],
  "TotalScoreCount": 50
}

How do i make it into this

studentId   Score  ScoreSource  StudentName Phone  classId Subject
    1         30     homework       Doe       911    01      math
    1         20     homework       Doe       911    01      math
...

Let's say i have 10 student

JSON functions are higly vendor-specific. Please tag your question with the database you are running: mysql, oracle, sqlserver...? — GMB
– GMB, Commented Nov 20, 2020 at 10:33

Useme Alehosaini · Accepted Answer · 2020-11-20 10:32:11Z

0

use OPENJSON to parse and transform

https://learn.microsoft.com/en-us/sql/relational-databases/json/convert-json-data-to-rows-and-columns-with-openjson-sql-server?view=sql-server-ver15

answered Nov 20, 2020 at 10:32

Useme Alehosaini

3,1266 gold badges22 silver badges28 bronze badges

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

1 Comment

Useme Alehosaini Over a year ago

@BQ Learning Check this stackoverflow.com/questions/47104722/… . I trust it give you a solution.

Collectives™ on Stack Overflow

How to split JSON file into multiple rows in SQL

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related