0

I am trying to get all records that contain WEAPON_COMBATPISTOL in the column inventory within my users table. It is based on JSON. I am lost I have tried JSON_EXTRACT & JSON_CONTAINS.

JSON DATA:

[{"slot":1,"count":450,"name":"money"},
{"slot":2,"count":54,"name":"ammo-9"},
{"metadata":{"serial":"643280CXJ213639","durability":97.91999999999998,"registered":"Barry McCeiner","components":[],"ammo":11},"slot":3,"count":1,"name":"WEAPON_COMBATPISTOL"},
{"slot":4,"count":8,"name":"burger"},
{"slot":5,"count":8,"name":"icetea"},
{"slot":6,"count":7,"name":"stone"},
{"slot":10,"count":6,"name":"lockpick"}]

SQL Statement:

SELECT * FROM users WHERE JSON_CONTAINS(inventory, 'WEAPON_COMBATPISTOL', '$.name');

I also want to remove just the WEAPON_COMBATPISTOL from the JSON data. If that is possible.

Improve this question
3
  • why the Lua tag? Commented Oct 20, 2021 at 10:42
  • It was because I am doing this for FiveM (Lua Based) Programming. Removed. Commented Oct 20, 2021 at 11:04
  • You cant use JSON_CONTAINS because you have a list of different possible paths. Could this be a duplicate of How to search nested JSON in MySQL? Commented Oct 20, 2021 at 16:38
Related questions
1
select query for json in sql
1
Select data from JSON
0
How do I select all rows that have a particular key in a JSON field?
Load 7 more related questions Show fewer related questions

0

Reset to default

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.