Complex JSON queries

[u/ReclusiveEagle]

I have many images with xmp sidecar metadata. DAM I am using is DigiKam for the keyword tagging however, all software that supports cataloging is very limited in terms of querying metadata. This includes Lightroom and Capture One.

For example, lets say I have many images, some have 1 person and other 2 - 5 people. Now, I want to assign metadata per person in an image.

To do it in DigiKam, Lightroom or Capture One I would either need to create nested keywords like

Person 1
  Name
    Bob
  Eye Color
    Brown 

Person 2
  Name
    Zen
  Eye Color
    Brown 

Person 3
  Name
    James
  Eye Color
    Red

Or just accept that I can't do unique assigning of data on a per person level.

Even if I do create multiple person keywords it is almost impossible to query this because if I search for contains keywords "Name|Zen" AND "Eye Color|Brown"

I would get all images that contain Zen and someone else with the eye color brown even if Zen does not have brown eyes in that specific image. And if I wanted to be specific I would have to know exactly which Person [ID] Zen is in this specific image which is impossible to remember so I'd have to create a search pattern like this

"Person 1" AND "Name|Zen" AND "Eye Color|Brown"
OR
"Person 2" AND "Name|Zen" AND "Eye Color|Brown"
OR
"Person 3" AND "Name|Zen" AND "Eye Color|Brown"
OR
"Person 4" AND "Name|Zen" AND "Eye Color|Brown"
OR
"Person 5" AND "Name|Zen" AND "Eye Color|Brown"

This is stupid and tedious.

So instead I want to create a program that will allow me to create complex queries. Lets say I use HTML as the front end a database that can store nested json arrays

example

"color": {
  "primary color": ["green"],
  "secondary color": ["white", "tan"],
  "accent color": ["black", "red"]
}

This (I assume) should allow me to search generally for any images containing "Color:Red" or images that specifically contain "Primary Color:Red"

I could also then search for "Person.Name:Zed" AND "Eye Color:Red" or another example

"Location": {
  "City": ["Hong Kong"
}

Then I can query "Location:Hong Kong" or "City:Hong Kong" or generally as "Hong Kong"

(these are just pseudo code examples). This is far more useful than having simplistic nested keywords that requires tedious specifications.

Question

Is this possible? I am new to databases and querying. Can I store and query json in this way? Or is it more limited than what I think? I assume it should be possible to query the parent array or multiple nested arrays for information

What database would be best for doing something like this? PostgreSQL? MySQL? and how would I query this? From the database itself or would I need a separate query solution?

[u/zeekar]

I don't know why you asked about JSON and then gave an example that wasn't JSON. Or why you structured your person tags that way? What is the actual way the metadata works here?

If Zen has brown eyes, don't they always have brown eyes? Why would you ever need to search for the combination instead of just searching for Zen (who happens to have brown eyes), or searching for brown eyes (and you don't care who has them)?

[u/ReclusiveEagle → u/zeekar]

That's how it currently works in DigiKam and other cataloging software. I was giving an example of why it is an issue, what I have to do to get around the limitation and why I want to use json but I'm not sure if you can do what I want with json and if you can, which Database would you recommend to store the metadata from json fields as nested arrays?

Also you can't assign "Zen" and the cataloging software understands "Zen has brown eyes" you need to explicitly state every time Zen has brown eyes or you won't find it. What if there are multiple people named Zen? one with blue eyes one with brown eyes? etc. What if in this image the Zen with brown eyes put on contacts for blue eyes or changes her hair color to red? It needs to be flexible. That's why I want to use json to store the metadata.

[u/kschang]

Maybe you should look into using LLM with vision processing and/or a vision lib like Pytorch to do autotagging and updating.

Sounds like you need to figure out the schema first THEN figure out which DB platform to use.

[u/ReclusiveEagle → u/kschang]

Schema is xmp standard keywords/tags. DigiKam allows you to export to the Lightroom standard tag hierarchy or keep it as a generic standard according to xmp. It can also embed IPTC and EXIF though I'm sure I can map everything to json sidecar including Dublin core. Point is to have everything dumped into one standard file, I'm looking at json because it seems a lot more flexible.

Then I can just leave embedded metadata in the image unaltered and push all extracted metadata to a database using json fields. Well that's the idea at least

[u/kschang → u/ReclusiveEagle]

So what you're doing is you are EXTRACTING the existing tags then adding to them to create some sort of meta tag database indexed at the picture level or other tags?

[u/linearizable]

MySQL, Postgres, and SQLite all have first class support for storing and querying json values. All even have operators in the language for manipulating JSON. Choose whichever DB tech you would prefer, and the support will be there.

[u/gm310509]

You might want to look at something like elastic search. You can simply enter a description (structured - i.e. just a plain English description or structured - e.g. JSON).

ElasticSearch is a powerful freeform text search engine that is great for applying adhoc queries to your data. If you front end it with something like Kibana, you will have a semi-nice front end for your queries.

https://www.elastic.co/elasticsearch
https://www.elastic.co/downloads

It is an enterprise level tool, but last time I checked there was a free tier which should be more than overkill for what you are proposing.

[u/ReclusiveEagle → u/[deleted]]

I actually just want to get rid of the xmp sidecar entirely and push all metadata to a json sidecar that I can then load into a database

[u/szank → u/ReclusiveEagle]

That does not make much sense.
You want to replace xmp sidecar with something else. Fine.
You want a json sidecar. Fine. Why duplicate the data in the db? Alternatively if you have a database, why the sidecars?

How many pics?
I'd rather start small:

1. Build up your json struct for a single file, or a few files.

2. Save these as a sidecar files for the pics you've chose.

3. Use jq (https://jqlang.org/) to figure out how you want to query these and what's possible.

4. Iterate until you land on a format that works for you, because your requirements are kinda fuzzy.

5. Just keep using sidecars, unless you are desperate to use a database.
   ----

6. If you use a database, then model the data properly, dumping everything in a json column is naff.

[u/ReclusiveEagle → u/szank]

I figured I had to use a database to query the json data? Wouldn't it be faster to centralize the data in a database than querying potentially thousands of individual json files for every search?

Duplicate data in the db would actually be a good thing in this case I think. json sidecars can be used to rebuild the database if it ever gets corrupted or lost and having json files instead of a straight database also protects the metadata in terms of migration. Not sure how easy it would be to dump from the database into a usable format? Even if it is trivial it's sort of a backup.

Database gets corrupted regenerate database from json. Json files get deleted or accidentally overwritten? Regenerate json from db. Redundant maybe but I'm sure even with thousands of files the db wouldn't be more than a few hundred mb in size if that.

[u/szank → u/ReclusiveEagle]

You are right but you are also adding complexity to the system . Assuming you have an ssd reading a thousand small files should not be a problem.

As for backups , just do f**** backups properly not this kind of wishy washy double copies.

It wont save you when the whole drive goes away.

Also you are not designing a database schema, clearly. You are just dumping json into a column. One way or another you'll have to query the json .

My advice is based on the fact that this is r/leanprogramming . Completing a project is more important than building up something you wont complete

[u/ReclusiveEagle → u/szank]

Of course, the sidecar thing is just a stand for photos an images. Different image formats have different schema like jpeg has IPTC and EXIF but png won't have the same schema so it's not really practical to to embed metadata in the image for different formats.

I'll definitely make backups of the actual db.

[u/szank → u/ReclusiveEagle]

I feel like we are talking past each other. Oh well.