How to Query JSON Within A Database

I would like to query information from databases that were created in this format:

index	label	key	data
1	sneaker	UPC	{“size”: “value”, “color”: “value”, “location”: “shelf2”}
2	location	shelf2	{“height”: “value”, “row”: “value”, “column”: “value”}

Where a large portion of the data is in one cell stored in a json array. To make matters a bit tricky, the attributes in json aren’t in any particular order, and sometimes they reference other cells. Ie in the above example there is a “location” attribute which has more data in another row. Additionally sometimes the data cell is a multidimensional array where values are nested inside another json array.

I’m seeking to do certain query tasks like

• Find all locations that have a sneaker
• Or find all sneakers with a particular color etc

What’s the industry accepted solution on how to do this?

These are sqlite databases that I’m currently using DB Browser for SQLite to query. Definitely open to better solutions if they exist.

Answer

The design that you have needs SQLite’s JSON1 extension.

The tasks that you mention in your question can be accomplished with the use of functions like json_extract().

Find all locations that have a sneaker

SELECT t1.*
FROM tablename t1
WHERE t1.label = 'location'
  AND EXISTS (
    SELECT 1 
    FROM tablename t2
    WHERE t2.label = 'sneaker'
      AND json_extract(t2.data, '$.location') = t1.key
  )

Find all sneakers with a particular color

SELECT *
FROM tablename
WHERE label = 'sneaker'
  AND json_extract(data, '$.color') = 'blue'

See the demo.

For more complicated tasks, such as getting values out of json arrays there are other functions like json_each().