Compare two JSON files and keep only matching using jq/bash-CodePudding

I have two files (I am copying just a small part of them as they are rather large):

_titles2.txt: (small sample)

{
  "titleId": "0100000000010800"
}
{
  "titleId": "010000000EEF0800"
}
{
  "titleId": "0100000011D90800"
}
{
  "titleId": "010000001260E800"
}
{
  "titleId": "01000000160F6800"
}
{
  "titleId": "010000100C4B8800"
}

titles.json (again just a small sample)

{
    "1": {
        "bannerUrl": null,
        "category": null,
        "description": null,
        "developer": null,
        "frontBoxArt": null,
        "iconUrl": null,
        "id": "0100000000000816",
        "intro": null,
        "isDemo": null,
        "key": null,
        "languages": null,
        "name": null,
        "nsuId": null,
        "numberOfPlayers": null,
        "publisher": null,
        "rank": 34342,
        "rating": null,
        "ratingContent": null,
        "region": null,
        "regions": null,
        "releaseDate": null,
        "rightsId": null,
        "screenshots": null,
        "size": 0,
        "version": null
    },
    "2": {
        "bannerUrl": "https://img-eshop.cdn.nintendo.net/i/c42553b4fd0312c31e70ec7468c6c9bccd739f340152925b9600631f2d29f8b5.jpg",
        "category": [
            "Platformer",
            "Action"
        ],
        "description": "Explore incredible places far from the Mushroom Kingdom as you join Mario and his new ally Cappy on a massive, globe-trotting 3D adventure. Use amazing new abilities\u2014like the power to capture and control objects, animals, and enemies\u2014to collect Power Moons so you can power up the Odyssey airship and save Princess Peach from Bowser\u2019s wedding plans!\n\nThanks to heroic, hat-shaped Cappy, Mario\u2019s got new moves that\u2019ll make you rethink his traditional run-and-jump gameplay\u2014like cap jump, cap throw, and capture. Use captured cohorts such as enemies, objects, and animals to progress through the game and uncover loads of hidden collectibles. And if you feel like playing with a friend, just pass them a Joy-Con\u2122 controller! Player 1 controls Mario while Player 2 controls Cappy. This sandbox-style 3D Mario adventure\u2014the first since 1996\u2019s beloved Super Mario 64\u2122 and 2002\u2019s Nintendo GameCube\u2122 classic Super Mario Sunshine\u2122\u2014is packed with secrets and surprises, plus exciting new kingdoms to explore.",
        "developer": null,
        "frontBoxArt": null,
        "iconUrl": "https://img-eshop.cdn.nintendo.net/i/ad4d31f664a1ce704f0219da2805f8459595bc3c01c3f04df2e32ba34a05b8c6.jpg",
        "id": "0100000000010000",
        "intro": "Embark on a cap-tivating, globe-trotting adventure",
        "isDemo": false,
        "key": null,
        "language": "en",
        "languages": [
            "ja",
            "en",
            "es",
            "fr",
            "de",
            "it",
            "nl",
            "ru",
            "zh",
            "zh"
        ],
        "name": "Super Mario Odyssey\u2122",
        "nsuId": 70010000001130,
        "numberOfPlayers": 2,
        "publisher": "Nintendo",
        "rank": 2,
        "rating": 10,
        "ratingContent": [
            "Cartoon Violence",
            "Comic Mischief"
        ],
        "region": "US",
        "regions": [
            "CO",
            "AR",
            "CL",
            "PE",
            "KR",
            "HK",
            "NZ",
            "BE",
            "CZ",
            "DK",
            "ES",
            "FI",
            "HU",
            "NL",
            "PL",
            "RU",
            "ZA",
            "MX",
            "CA",
            "FR",
            "DE",
            "JP",
            "AU",
            "GB",
            "US"
        ],
        "releaseDate": 20171027,
        "rightsId": "01000000000100000000000000000003",
        "screenshots": [
            "https://img-eshop.cdn.nintendo.net/i/c497547957d9dd3668e891aa97ff4899a3f40bd1bd430020f8cbdf673f02bdeb.jpg",
            "https://img-eshop.cdn.nintendo.net/i/1839d571921e3fb19ef48da64c145cb8ce573b07d7390c6350f15291b3905048.jpg",
            "https://img-eshop.cdn.nintendo.net/i/d0510e4804381287c64dc3ab374ae9273419e263e9a732e72af3b39cd70f2b5f.jpg",
            "https://img-eshop.cdn.nintendo.net/i/ab713d841b0f0d9bbce38b589d75dd3bba2aef005418e59d3f0fd95389ae7016.jpg",
            "https://img-eshop.cdn.nintendo.net/i/abcf9708cc28ef5b7ab412aa17fafe4ad181f0b957456ffd9b83de2561d9f62a.jpg",
            "https://img-eshop.cdn.nintendo.net/i/7db70ecd883f94d935f65b9f4454b8151b6f8be7c4be95543e87488e48e5a6b9.jpg"
        ],
        "size": 6026166272,
        "version": 0
    },
    "3": {
        "bannerUrl": null,
        "category": null,
        "description": null,
        "developer": null,
        "frontBoxArt": null,
        "iconUrl": null,
        "id": "0100000000010800",
        "intro": null,
        "isDemo": null,
        "key": null,
        "languages": null,
        "name": null,
        "nsuId": null,
        "numberOfPlayers": null,
        "publisher": null,
        "rank": 34341,
        "rating": null,
        "ratingContent": null,
        "region": null,
        "regions": null,
        "releaseDate": null,
        "rightsId": null,
        "screenshots": null,
        "size": 0,
        "version": 262144
    },
    "4": {
        "bannerUrl": null,
        "category": null,
        "description": null,
        "developer": null,
        "frontBoxArt": null,
        "iconUrl": null,
        "id": "010000000E5EE000",
        "intro": null,
        "isDemo": null,
        "key": null,
        "languages": null,
        "name": null,
        "nsuId": null,
        "numberOfPlayers": null,
        "publisher": null,
        "rank": 34340,
        "rating": null,
        "ratingContent": null,
        "region": null,
        "regions": null,
        "releaseDate": null,
        "rightsId": null,
        "screenshots": null,
        "size": 0,
        "version": 0
    },
    "5": {
        "bannerUrl": "https://img-eshop.cdn.nintendo.net/i/74dbbc2f5dd60e8c671a0c0a1ad18034e80e26375589765e90449ea4b5e15739.jpg",
        "category": [
            "Adventure",
            "Action",
            "Simulation"
        ],
        "description": "Explore a darkened office complex and uncover the truth behind the horrifying events that have befallen its inhabitants.\nPlay as one of two characters with unique storylines, monster encounters, and gameplay challenges. \nNavigate a multi-story maze that grows scarier, harder, and more twisted as you struggle to escape the madness.",
        "developer": null,
        "frontBoxArt": null,
        "iconUrl": "https://img-eshop.cdn.nintendo.net/i/4699a0d5b05a057a5d20c252d2d46a7eec5d41cfd46eb33e4e87b6247b3f7486.jpg",
        "id": "010000000EEF0000",
        "intro": "Shadows 2: Perfidia is a survival horror game inspired by classics like the Penumbra series and Layers of Fear.",
        "isDemo": false,
        "key": null,
        "language": "en",
        "languages": [
            "en"
        ],
        "name": "Shadows 2: Perfidia",
        "nsuId": 70010000021900,
        "numberOfPlayers": 1,
        "publisher": "Ultimate Games",
        "rank": 2963,
        "rating": 17,
        "ratingContent": [
            "Blood and Gore",
            "Intense Violence",
            "Nudity",
            "Sexual Content",
            "Strong Language"
        ],
        "region": "US",
        "regions": [
            "BE",
            "CZ",
            "DK",
            "ES",
            "FI",
            "HU",
            "NL",
            "PL",
            "RU",
            "ZA",
            "MX",
            "CA",
            "FR",
            "DE",
            "AU",
            "GB",
            "US"
        ],
        "releaseDate": 20190806,
        "rightsId": null,
        "screenshots": [
            "https://img-eshop.cdn.nintendo.net/i/a40b9ad80b4d6ab05c2ebc5dbf16484e4074a3a2b32b1ca15505dde5d56ebdd5.jpg",
            "https://img-eshop.cdn.nintendo.net/i/4cd32eaf41ee287b4e9854646598c5b2feaf405d431977cf8f5002ef5a3fa428.jpg",
            "https://img-eshop.cdn.nintendo.net/i/e2547655c002df78f573e1a79afd39dc70defce4d7e33fb3b68820fb5468d89d.jpg",
            "https://img-eshop.cdn.nintendo.net/i/85d23d86660204f1f6fb14feece746e12860ce26944cff47ca407b53e8f2c686.jpg",
            "https://img-eshop.cdn.nintendo.net/i/e499c7e25643ed0366cfdcff9c14da9cc5a528f841aba9be7188b531ef2fe219.jpg",
            "https://img-eshop.cdn.nintendo.net/i/6b36b07122f2a4b8ac34df656159ce2e4020a6c19c06f8cdcafc221c9b4b5598.jpg"
        ],
        "size": 2687500288,
        "version": 0
    },

Using jq, I want to test wether each objects 'titleId' in _titles2.txt exists in titles.json as 'id'. If it does exist, I would like for a third json file to be created with all the "matches" that were made.

I was able to do this with a single 'titleId' but can't seem to figure out how to do it for the full file (Other than doing a huge for loop and testing each individually)

Hoping someone could help with this! Thank you.

CodePudding user response：

If I understand the question properly, there is no need to call jq more than once, or to write a shell loop.

For example, if your goal is to produce a single JSON object, you could go with:

< titles.json jq --slurpfile titleIds _titles2.txt '
  INDEX($titleIds[]; .titleId) as $dict
  | with_entries( select($dict[.value.id]))
'

CodePudding user response：

Depending on what you mean by

I want to test wether each objects […] exists [and] if it does exist, I would like for a third json file to be created with all the "matches" that were made.

If this means that the IDs in the second file may occur multiple times, and you want to collect all occurrences of a specific ID into its own file, iteratively for each ID specified in the first file, you could go with looping over the first file's values in Bash, filter out non-matching fields in the second file, then check against the empty object {}, and create the respective file:

while read -r id; do
  json="$(< titles.json jq --arg id "$id" '
    with_entries(select(.value.id == $id))
  ')"
  [[ "$json" == '{}' ]] || cat <<< "$json" > "$id.json"
done < <(jq -r '.titleId' _titles2.txt)

You could also move the loop into jq, and use additional calls to jq only to pretty-print (without, you'd get one-line JSON files):

while read -r id json; do
  # cat <<< "$json" > "$id.json"
  jq . <<< "$json" > "$id.json"
done < <(jq -r '
  inputs.titleId as $id | with_entries(select(.value.id == $id))
  | select(. != {}) | "\($id) \(.)"
' titles.json _titles2.txt)

For the sample files given, only one ID (namely 0100000000010800) produces any matches, and it also matches just one target field (namely 3). So, the only file created by either approach is called 0100000000010800.json, and has the following content:

{
  "3": {
    "bannerUrl": null,
    "category": null,
    "description": null,
    "developer": null,
    "frontBoxArt": null,
    "iconUrl": null,
    "id": "0100000000010800",
    "intro": null,
    "isDemo": null,
    "key": null,
    "languages": null,
    "name": null,
    "nsuId": null,
    "numberOfPlayers": null,
    "publisher": null,
    "rank": 34341,
    "rating": null,
    "ratingContent": null,
    "region": null,
    "regions": null,
    "releaseDate": null,
    "rightsId": null,
    "screenshots": null,
    "size": 0,
    "version": 262144
  }
}