Clash Royale CLAN TAG#URR8PPP

How to extract the values from nested json structure in my sql? [closed]

i have my json like this

my column name is data

data

[

"DetailMaster":
"DetailsID":"FD001",
"Number":"9W82Q",
"RegNumber":"SF AAV",
"Hours":"4000"
,
"DetailChild":[

"subDetailsID":"FD001_Sub01",
"DetailsID":"FD001",
"model":"B737"

]

]


i just want to get the column data by providing the Number and RegNumber in json. how can i do that

This question appears to be off-topic. The users who voted to close gave this specific reason:

1 Answer
1

It can be achieved in two methods.

1)using JSON_EXTRACT

mysql> SELECT c, JSON_EXTRACT(c, "$.id"), g
> FROM jemp
> WHERE JSON_EXTRACT(c, "$.id") > 1
> ORDER BY JSON_EXTRACT(c, "$.name");


2)Using column->>path

The -> operator serves as an alias for the JSON_EXTRACT() function when used with two arguments, a column identifier on the left and a JSON path on the right that is evaluated against the JSON document (the column value). You can use such expressions in place of column identifiers wherever they occur in SQL statements.

mysql> SELECT c, c->"$.id", g
> FROM jemp
> WHERE c->"$.id" > 1
> ORDER BY c->"$.name";


where c is the column of a JSON.
for more reference kindly go through the MySQL official site.