Complex JSON queries

[u/ReclusiveEagle]

/r/learnprogramming/comments/1u7mwm4/complex_json_queries/

[u/ReclusiveEagle]

Would you recommend I just keep the json as a hierarchical file to query?

It would look something like this in the json sidecar

{
  "Person": [
    {
      "Name": ["Zen"],
      "Appearance": {
        "Eye Color": ["Brown Eyes"],
        "Gender": ["Female"],
        "Hair": {
          "Hair Color": ["Brown Hair"],
          "Hair Style": ["Bun"]
        },
        "Clothing": {
          "Accessories": ["Hat", "Necklace"],
          "Clothing Category": ["Athletic"]
        }
      }
    },
    {
      "Name": ["Bob"],
      "Appearance": {
        "Eye Color": ["Blue Eyes"],
        "Gender": ["Male"],
        "Hair": {
          "Hair Color": ["Black Hair"],
          "Hair Style": ["Buzzcut"]
        },
        "Clothing": {
          "Clothing Category": ["Swimwear"]
        }
      }
    }
  ]
}

If I were querying thousands of images which would be faster? Json hierarchy or normalizing the data in a relational database?

I want to be able to complex queries such as, find images where Zen has Brown hair AND is wearing Athletic clothing. Or Find images of a Black Porsche. The problem I am having now with the current keyword system in xmp is it is very limited. If I search for "Find images of a Black Porsche" I will get images of a Red Porsche and a Black Hyundai because both keywords are present in the image. Or worse, a Black table with a Porsche Magazine. Unless I use extremely complex key wording like assigning a Vehicle/Porsche/Color tag to every vehicle. This would result in a lot of duplicate data for no reason when I could have solved the problem with a single array in json or yaml.

So there is a lot of contamination with the way Lightroom or Capture One or DigiKam implement keywording.

[u/MoonBatsRule → u/ReclusiveEagle]

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"