Search a JSON column in a database-CodePudding

I'm looking to see if it's possible to search multiple database rows for a specific value that's stored in a json string. For instance I have a table called stashitems that contains a json column items that stores all of a players items. I would like to search the 1500 rows of data for a specific label or name value. Below is a snippet of one players stashitems.

How could I accomplish this for the entire table? Thanks for any help!

{
"8": {
    "type": "item",
    "slot": 8,
    "amount": 948,
    "weight": 100,
    "name": "glass",
    "label": "Glass",
    "image": "glass.png",
    "useable": false,
    "unique": false,
    "info": ""
},
"23": {
    "type": "item",
    "slot": 23,
    "amount": 1,
    "weight": 200,
    "name": "crack_baggy",
    "label": "Bag of Crack",
    "image": "crack_baggy.png",
    "useable": true,
    "unique": false,
    "info": ""
},
"47": {
    "type": "item",
    "slot": 47,
    "amount": 1,
    "weight": 20000,
    "name": "diving_gear",
    "label": "Diving Gear",
    "image": "diving_gear.png",
    "useable": true,
    "unique": true,
    "info": []
},
"48": {
    "type": "item",
    "slot": 48,
    "amount": 1,
    "weight": 20000,
    "name": "diving_gear",
    "label": "Diving Gear",
    "image": "diving_gear.png",
    "useable": true,
    "unique": true,
    "info": []
}
}

MariaDB 10.4.22

CodePudding user response：

In SQL Server, you can very easily traverse JSON with the following syntax:

SELECT 'stashitems'
  JSON_VALUE(json_column, '$.8.type') AS type

see this documentation.

It's also possible in MySQL, though the syntax is different.

Unfortunately, SQLite stores only text values, so a workaround is needed.

CodePudding user response：

There are some possibilities like

SELECT JSON_SEARCH(@json, 'all', 'glass');

| JSON_SEARCH(@json, 'all', 'glass') |
| :--------------------------------- |
| ["$.8.name", "$.8.label"]          |

SELECT @json Like '%glass%'

| @json Like '%glass%' |
| -------------------: |
|                    1 |

db<>fiddle here