I've been using Lightroom for a few years. At one point I kept keywords in a hierarchy, so I'd have something like:

Animals > Birds > Eagles

This worked well, I could include the option to export parent keywords and all was good. Then I got lazy and started tagging things all over the place. So whereas my "Eagles" tag is nicely under Animals and Birds, I might have a Falcons or Parrot keyword that stands on its own and doesn't have the parental structure.

Rearranging the keyword hierarchy within Lightroom is very time-consuming... dragging and dropping isn't so quick when there are thousands of items in play.

I know that Lightroom has the ability to export and import keywords, but I'm struggling with the best way to perhaps clean this up. Is there a good way to manipulate the keywords externally and then bring that back into Lightroom?

Oh boy I feel your pain. I'm in a similar situation, after importing thousands of JPEG files I had properly (and heavily) tagged on Flickr. The keyword UI in LR is really difficult and slow to use, even with a hierarchical structure. Keywords are, however, stored just like the rest of LR's data, in its SQLite database (i.e. the LR catalog). I contemplated writing a small app in Qt to manipulate the keywords, but the issue is that I also need to see the corresponding photos when I cleanup or reorganize my keywords, say, to see if that keyword is really needed or properly applied for example.
–
sebastien.bMar 16 '11 at 5:45

I'm not too worried about the photo association - I just want to heirarchy-ize the keywords, leaving them associated with their respective photos.
–
ahockleyMar 16 '11 at 14:16

3 Answers
3

Like Sebastien mentioned, the library file is SQLite. I dug around in my own catalog and found that AgLibraryKeyword table. There are three columns you care about: id_local, name, and parent. As you might expect, parent points to the id_local of the parent keyword. In my library the "root" keyword has id_local 73.

UPDATE AgLibraryKeyword
SET parent = (SELECT id_local FROM AgLibraryKeyword WHERE name = 'animals')
WHERE name = 'birds';
UPDATE AgLibraryKeyword
SET parent = (SELECT id_local FROM AgLibraryKeyword WHERE name = 'birds')
WHERE name IN ( 'eagles', 'parrots', 'falcons' );

This would end up as Animals > Birds > Eagles, Animals > Birds > Parrots, and so on. The table ends up looking like this:

It's not as manual as doing it by hand through the UI, but it's still going to take some work. Should be just as functional though.

I should note that while investigating this, I made similar changes a few times that didn't seem to take. I'd suggest saving your SQL to a file so you can re-run it if it doesn't seem to work the first time. And, of course, you should back up your library ahead of time :)

If you want to edit the hierarchy using SQLite as suggested in bleything's answer, note that (at least in LR4; not sure about 3) the keyword hierarchy is actually represented in two different places in the AgLibraryKeyword table:

The genealogy column is a full dump of the nodes of the hierarchy, with nodes delimited by forward slashes and with the first digit of each entry indicating the number of digits in the node's ID. In the excerpt above, the root node is ID 40, the top-level keyword in my hierarchy is ID 802186, etc.

You will probably want to make sure that column as well as the parent column is updated correctly if you're manually manipulating the hierarchy in SQLite.

Why they include the number of digits in the node ID is unclear to me; my best guess is it's to support LIKE queries to quickly return subtrees without risking ambiguity between IDs of different lengths, but they could just as easily have done that in less space by requiring an ending delimiter, so maybe there's another reason.