Applying simple string mappings on JSON files

Question

Somehow I think there must be a one-liner to apply a simple mapping on the command line. In this case the keys in JSON will (as usual) provide context, ensuring that we don't foolishly replace strings that shouldn't.

Suppose we are given a library catalog in a JSON file using the Dewey Decimal Classification

[
  {
    "Title": "Design Pattern",
    "Call Number": "005.12 DES"
  },
  {
    "Title": "Intro to C++",
    "Call Number": "005.133 C STR"
  }
]

as well as a mapping between Dewey and the Library of Congress call numbers

[
  {
    "Dewey": "005.12 DES",
    "Congress": "QA76.64 .D47 1995X"
  },
  {
    "Dewey": "005.133 C STR",
    "Congress": "QA76.73.C153 S77 2013"
  }
]  

and want to produce the output file:

[
  {
    "Title": "Design Pattern",
    "Call Number": "QA76.64 .D47 1995X"
  },
  {
    "Title": "Intro to C++",
    "Call Number": "QA76.73.C153 S77 2013"
  }
]

Does this still fit within the one-line set of transformations that jq will handle?

Answer 1

The following jq command:

jq -s '[
    JOIN(
        INDEX(.[0][]; ."Call Number");
        .[1][];
        .Dewey;
        { Title: .[1].Title, "Call Number": .[0].Congress }
    )
]' catalog dewey_mappings

This reads the two files into an array as .[0] and .[1] respectively, using jq -s.

An index is computed of the first file's Call Number field. This index is used in a relational JOIN operation on the Dewey field of the second file.

From the pairs of elements that the JOIN pulls out, an object with a Title field taken from the second file's Title field, and a Call Number field taken from the first file's Congress field, is created. All created objects are put into an array (hence the [ and ] around the JOIN).

The result will be a JSON document like this:

[
  {
    "Title": "Design Pattern",
    "Call Number": "QA76.64 .D47 1995X"
  },
  {
    "Title": "Intro to C++",
    "Call Number": "QA76.73.C153 S77 2013"
  }
]

See also the section entitled "SQL-Style Operators" in the jq manual.

---

If it feels more natural to use the Dewey mappings file as the index and to query it with the Dewey codes from the catalog:

jq -s '[
    JOIN(
        INDEX(.[0][]; .Dewey);
        .[1][];
        ."Call Number";
        { Title: .[0].Title, "Call Number": .[1].Congress }
    )
]' dewey_mappings catalog