I have a table which storing JSON data in it,
Table Services
<code>| ID| Details | Time
| A | {a:"2",b:"2",c:"3"} | 16-06-2024 00:00:00 |
| A | {a:"2",b:"4",c:"5"} | 16-06-2024 00:30:00 |
| A | {a:"3",b:"4",c:"0"} | 16-06-2024 01:00:00 |
| A | {a:"3",b:"0",c:"0"} | 16-06-2024 01:30:00 |
| B | {a:"6",b:"2",c:"2"} | 16-06-2024 00:00:00 |
| B | {a:"2",b:"4",c:"6"} | 16-06-2024 00:30:00 |
| B | {a:"3",b:"4",c:"0"} | 16-06-2024 01:00:00 |
| B | {a:"3",b:"0",c:"0"} | 16-06-2024 01:30:00 |
</code>
<code>| ID| Details | Time
| A | {a:"2",b:"2",c:"3"} | 16-06-2024 00:00:00 |
| A | {a:"2",b:"4",c:"5"} | 16-06-2024 00:30:00 |
| A | {a:"3",b:"4",c:"0"} | 16-06-2024 01:00:00 |
| A | {a:"3",b:"0",c:"0"} | 16-06-2024 01:30:00 |
| B | {a:"6",b:"2",c:"2"} | 16-06-2024 00:00:00 |
| B | {a:"2",b:"4",c:"6"} | 16-06-2024 00:30:00 |
| B | {a:"3",b:"4",c:"0"} | 16-06-2024 01:00:00 |
| B | {a:"3",b:"0",c:"0"} | 16-06-2024 01:30:00 |
</code>
| ID| Details | Time
| A | {a:"2",b:"2",c:"3"} | 16-06-2024 00:00:00 |
| A | {a:"2",b:"4",c:"5"} | 16-06-2024 00:30:00 |
| A | {a:"3",b:"4",c:"0"} | 16-06-2024 01:00:00 |
| A | {a:"3",b:"0",c:"0"} | 16-06-2024 01:30:00 |
| B | {a:"6",b:"2",c:"2"} | 16-06-2024 00:00:00 |
| B | {a:"2",b:"4",c:"6"} | 16-06-2024 00:30:00 |
| B | {a:"3",b:"4",c:"0"} | 16-06-2024 01:00:00 |
| B | {a:"3",b:"0",c:"0"} | 16-06-2024 01:30:00 |
I am searching ways to
first, using cursor to use [JSON_EXTRACT(Services, '$.(JSON keys)') as (JSON keys)]
to turn the JSON keys as column name and JSON values as values respectively
secondly, using SUBSTRING to get hourly average.
I want the result like this:
ID | a | b | c | Time |
---|---|---|---|---|
A | 2 | 3 | 4 | 16-06-2024 00:00:00 |
A | 3 | 2 | 0 | 16-06-2024 01:00:00 |
B | 4 | 3 | 4 | 16-06-2024 00:00:00 |
B | 3 | 2 | 0 | 16-06-2024 01:00:00 |
New contributor
Hokyao Chan is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.