MYSQL JSON column has array values built into the array,
I want to retrieve indexes containing certain values from this array with mysql commands.
JSON column;
id=1 // There is only one row in the table
<code>[
[57,0,0,0,0,53,"Eylül - 2024",0,0,[0,0,1500,""]],
[56,7,0,17267904,0,53,"Eylül - 2024","0","1",[9,1262,238,"1~0003"]],
[55,7,0,17267904,0,53,"Eylül - 2024","0","1",[9,1426,74,1]],
[54,6,0,17267904,0,53,"Eylül - 2024","0","8",[9,1426,74,1]],
[53,4,0,17267904,0,53,"Eylül - 2024","0","6",[9,1426,74,1]],
[52,7,0,17267904,0,53,"Eylül - 2024","0","1",[9,1426,74,1]],
[51,7,0,17267904,0,53,"Eylül - 2024","0","5",[9,1426,74,1]]
.....
]
</code>
<code>[
[57,0,0,0,0,53,"Eylül - 2024",0,0,[0,0,1500,""]],
[56,7,0,17267904,0,53,"Eylül - 2024","0","1",[9,1262,238,"1~0003"]],
[55,7,0,17267904,0,53,"Eylül - 2024","0","1",[9,1426,74,1]],
[54,6,0,17267904,0,53,"Eylül - 2024","0","8",[9,1426,74,1]],
[53,4,0,17267904,0,53,"Eylül - 2024","0","6",[9,1426,74,1]],
[52,7,0,17267904,0,53,"Eylül - 2024","0","1",[9,1426,74,1]],
[51,7,0,17267904,0,53,"Eylül - 2024","0","5",[9,1426,74,1]]
.....
]
</code>
[
[57,0,0,0,0,53,"Eylül - 2024",0,0,[0,0,1500,""]],
[56,7,0,17267904,0,53,"Eylül - 2024","0","1",[9,1262,238,"1~0003"]],
[55,7,0,17267904,0,53,"Eylül - 2024","0","1",[9,1426,74,1]],
[54,6,0,17267904,0,53,"Eylül - 2024","0","8",[9,1426,74,1]],
[53,4,0,17267904,0,53,"Eylül - 2024","0","6",[9,1426,74,1]],
[52,7,0,17267904,0,53,"Eylül - 2024","0","1",[9,1426,74,1]],
[51,7,0,17267904,0,53,"Eylül - 2024","0","5",[9,1426,74,1]]
.....
]
The 9th elements of the arrays in the row are the row numbers of the people in the recording story. For example, I want to take all the elements of the arrays whose 9th element is 1 and create a new array and use it.
<code> [52,7,0,17267904,0,53,"Eylül - 2024","0","/*
The person I'm looking for =*/ 1",[9,1426,74,1]],
</code>
<code> [52,7,0,17267904,0,53,"Eylül - 2024","0","/*
The person I'm looking for =*/ 1",[9,1426,74,1]],
</code>
[52,7,0,17267904,0,53,"Eylül - 2024","0","/*
The person I'm looking for =*/ 1",[9,1426,74,1]],
<code>sample result ;
[
[56,7,0,17267904,0,53,"Eylül - 2024","0","1",[9,1262,238,"1~0003"]],
[55,7,0,17267904,0,53,"Eylül - 2024","0","1",[9,1426,74,1]],
[52,7,0,17267904,0,53,"Eylül - 2024","0","1",[9,1426,74,1]]
]
</code>
<code>sample result ;
[
[56,7,0,17267904,0,53,"Eylül - 2024","0","1",[9,1262,238,"1~0003"]],
[55,7,0,17267904,0,53,"Eylül - 2024","0","1",[9,1426,74,1]],
[52,7,0,17267904,0,53,"Eylül - 2024","0","1",[9,1426,74,1]]
]
</code>
sample result ;
[
[56,7,0,17267904,0,53,"Eylül - 2024","0","1",[9,1262,238,"1~0003"]],
[55,7,0,17267904,0,53,"Eylül - 2024","0","1",[9,1426,74,1]],
[52,7,0,17267904,0,53,"Eylül - 2024","0","1",[9,1426,74,1]]
]
What I wrote is similar to this;
<code>$myQuery= $conn->query("
SELECT JSON_EXTRACT(vt_st_cari_haraket_json, '$[*][0]')
FROM vt_tb_cari_haraket_2024
WHERE JSON_CONTAINS(JSON_EXTRACT(vt_st_cari_haraket_json, '$[*][9][0][0]'), '1', '$')
");
</code>
<code>$myQuery= $conn->query("
SELECT JSON_EXTRACT(vt_st_cari_haraket_json, '$[*][0]')
FROM vt_tb_cari_haraket_2024
WHERE JSON_CONTAINS(JSON_EXTRACT(vt_st_cari_haraket_json, '$[*][9][0][0]'), '1', '$')
");
</code>
$myQuery= $conn->query("
SELECT JSON_EXTRACT(vt_st_cari_haraket_json, '$[*][0]')
FROM vt_tb_cari_haraket_2024
WHERE JSON_CONTAINS(JSON_EXTRACT(vt_st_cari_haraket_json, '$[*][9][0][0]'), '1', '$')
");
However, although I tried a variety of similar things, I could not get the result I wanted.
In summary; If the 9th element in the subarrays is the searched value, I want to get the entire array.
I want to select and retrieve arrays within array in MYSQL JSON column
New contributor
Özgür AKSU is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
<code>select json_arrayagg(j.a) as _result
from vt_tb_cari_haraket_2024
cross join json_table(vt_st_cari_haraket_json, '$[*]' columns(a json path '$')) as j
where j.a->>'$[8]' = '1';
</code>
<code>select json_arrayagg(j.a) as _result
from vt_tb_cari_haraket_2024
cross join json_table(vt_st_cari_haraket_json, '$[*]' columns(a json path '$')) as j
where j.a->>'$[8]' = '1';
</code>
select json_arrayagg(j.a) as _result
from vt_tb_cari_haraket_2024
cross join json_table(vt_st_cari_haraket_json, '$[*]' columns(a json path '$')) as j
where j.a->>'$[8]' = '1';
Result:
<code>[
[56, 7, 0, 17267904, 0, 53, "Eylül - 2024", "0", "1", [9, 1262, 238, "1~0003"]],
[55, 7, 0, 17267904, 0, 53, "Eylül - 2024", "0", "1", [9, 1426, 74, 1]],
[52, 7, 0, 17267904, 0, 53, "Eylül - 2024", "0", "1", [9, 1426, 74, 1]]
]
</code>
<code>[
[56, 7, 0, 17267904, 0, 53, "Eylül - 2024", "0", "1", [9, 1262, 238, "1~0003"]],
[55, 7, 0, 17267904, 0, 53, "Eylül - 2024", "0", "1", [9, 1426, 74, 1]],
[52, 7, 0, 17267904, 0, 53, "Eylül - 2024", "0", "1", [9, 1426, 74, 1]]
]
</code>
[
[56, 7, 0, 17267904, 0, 53, "Eylül - 2024", "0", "1", [9, 1262, 238, "1~0003"]],
[55, 7, 0, 17267904, 0, 53, "Eylül - 2024", "0", "1", [9, 1426, 74, 1]],
[52, 7, 0, 17267904, 0, 53, "Eylül - 2024", "0", "1", [9, 1426, 74, 1]]
]
Demo:
https://www.db-fiddle.com/f/fhMr8VtNjNY5B6QPyhS9JN/0
Note that the JSON_TABLE() function requires MySQL 8.0 or later.