jsoncsvselectjqexport-to-csv

Selecting the first item with a matching term in an array using JQ


I have the following JSON format:

    "MediaContainer": {
        "Metadata": [
            {
                "Key": "5d77682c151a60001f24bbe8",
                "Image": [
                    {
                        "alt": "image",
                        "type": "background",
                        "url": "https://image.tmdb.org/t/p/original/f8Q3Sug5Sa42psw2b3Gdd1aT8t.jpg"
                    },
                    {
                        "alt": "image",
                        "type": "clearLogoWide",
                        "url": "http://assets.fanart.tv/fanart/movies/2639/hdmovielogo/deconstructing-harry-60f13837ee6aa.png"
                    },
                    {
                        "alt": "image",
                        "type": "coverArt",
                        "url": "https://metadata-static.imdb.com/4/gracenote/44a1852151a726d730c214b97986c27e.jpg"
                    },
                    {
                        "alt": "image",
                        "type": "coverPoster",
                        "url": "https://image.tmdb.org/t/p/original/i7Z5DdznqANJUjqWISEFu9bw6J7.jpg"
                    },
                    {
                        "alt": "image",
                        "type": "coverSquare",
                        "url": "https://metadata-static.imdb.com/c/gracenote/c8c351b348baf43a909e4a3b1c60a443.jpg"
                    },
                    {
                        "alt": "image",
                        "type": "snapshot",
                        "url": "https://metadata-static.imdb.com/a/gracenote/ab5a905958de8d43c763d74153242690.jpg"
                    }
                ],
            },

I am trying to output TSV where each line contains the "Key" and the first "url" value in the "Image" array that contains the string "fanart". So for instance with the above JSON, the output I'm looking for is:

5d77682c151a60001f24bbe8 http://assets.fanart.tv/fanart/movies/2639/hdmovielogo/deconstructing-harry-60f13837ee6aa.png

Here's my failed attempt, which just spits out compile errors:

jq -r '.MediaContainer.Metadata[] | [.ratingKey,.Image[] | select( .url as $items | "fanart" | IN($items[]) )] | @tsv' 'trending.json'


Solution

  • You were quite close. Here we use first to obtain the first url that meets the condition, on the assumption that there is at least one:

    .MediaContainer.Metadata[]
    | [.Key, first(.Image[].url | select(test("fanart") ) ) ]
    | @tsv
    

    The above might still be acceptable if no .url satisfies the selection criterion, but if not, it should be easy to add your tweak.