Tuesday, September 14, 2021
Parse JSON in SQL - gitlab sample
In this sample you can see how easy is to parse data in JSON structure with SQL.
Sample SQL:
SELECT title, linkx, datex
FROM
JSON_TABLE( JSON_SOURCE , '$[*]'
COLUMNS
(title VARCHAR2(100) PATH '$.commit.message',
linkx VARCHAR2(100) PATH '$.html_url',
datex VARCHAR2(100) PATH '$.commit.author.date'
)
)
JSON_SOURCE (https://api.github.com/repos/DomenDolar/RASD/commits)
[
{
"sha": "86eaefb051ed496faad28860121d80a9597d0fd4",
"commit": {
"author": {
"name": "DD",
"email": "dd@dd.si",
"date": "2021-04-29T21:00:55Z"
},
"committer": {
"name": "DD",
"email": "dd@dd.si",
"date": "2021-04-29T21:00:55Z"
},
"message": "RASDC_BLOCKSONFORM\n20210303 ",
"url": "https://api.github.com/repos/DomenDolar/RASD/git/commits/86eaefb051ed496faad28860121d80a9597d0fd4",
},
"url": "https://api.github.com/repos/DomenDolar/RASD/commits/86eaefb051ed496faad28860121d80a9597d0fd4",
"html_url": "https://github.com/DomenDolar/RASD/commit/86eaefb051ed496faad28860121d80a9597d0fd4",
},
{
"sha": "bb72a90f6ec5b7ff3fc585d9555e727269eb5efb",
"commit": {
"author": {
"name": "DD",
"email": "dd@dd.si",
"date": "2020-09-28T22:08:24Z"
},
"committer": {
"name": "DD",
"email": "dd@dd.si",
"date": "2020-09-28T22:08:24Z"
},
"message": "Merge branch 'master' of https://github.com/DomenDolar/RASD into master",
},
"url": "https://api.github.com/repos/DomenDolar/RASD/commits/bb72a90f6ec5b7ff3fc585d9555e727269eb5efb",
"html_url": "https://github.com/DomenDolar/RASD/commit/bb72a90f6ec5b7ff3fc585d9555e727269eb5efb",
}
]
