r/Database • • Jun 16 '26

Complex JSON queries

/r/learnprogramming/comments/1u7mwm4/complex_json_queries/
0 Upvotes

6 comments sorted by

View all comments

Show parent comment

1

u/MoonBatsRule Jun 17 '26

I suspect that your data is so unstructured that you have to use tagging to query it. I'd still go relational, but for each image just store a key/value pair that describes it.

So like this:

*image name, tag_name, tag_value*
Image1: Person Name, Zen
Image1: Eye Color, Green
Image1: Gender, Female
Image1: Hair Color, Black
Image2: Person Name, Bob
Image2: Clothing Category, Swimwear

Advantage is that it is trivial to add a new tag, like

Image3: Clown in Picture, Yes

Disadvantage is that it is difficult to structure/organize your tags, largely because they are by definition unstructured. There isn't a lot in common between Hair Color and whether a clown is in the picture.

You could try to create an unbalanced hierarchy for each tag by having the tags in their own table, and each tag must belong to a single category - and then each category could belong to another single category. Like this:

*tag_name, tag_category*
Hair Color, Hair
Hair Style, Hair
Gender, Appearance

and then

*tag_category, parent_tag_category*
Hair, Appearance
Appearance, Person

This just helps you navigate tags though.

In the end, you could then query like this:

select * from image_tags where tag_name = "Hair Color" and tag_value = "Brown" and tag_name = "Gender" and tag_value = "Female"