[SOLVED] To extract field from JSON file comparing it with plain text file matching values and extract specific field from JSON file

Issue

I have file1.json and plain text file2, Where using file2 values compare with file.json with matching values of file2 there will be the corresponding field which is CaseID in file1.json the resultant file should consist of those values. I have mentioned cases below with expected results.

I was trying to extract using the awk tool, where I don't get my expected answer

 awk -F, 'FNR==NR {f2[$1];next} !($0 in f2)' file2 file1

file1.json

{
    "Cases": [{
            "CaseID": "100",
            "CaseUpdatedByUser": "XYZ",
            "Case": {
                "CaseName": "Apple",
                "ID": "1"
            }
        },
        {
            "CaseID": "350",
            "CaseUpdatedByUser": "ABC",
            "Case": {
                "CaseName": "Mango",
                "ID": "1"
            }
        },
        {
            "CaseID": "440",
            "CaseUpdatedByUser": "PQR",
            "Case": {
                "CaseName": "Strawberry",
                "ID": "1"
            }
        }
    ]
}

file2

Apple
Strawberry
Mango

Expected output:

100
350
440

Solution

With jq, awk and sort:

jq -r '.Cases[] | "\(.Case.CaseName);\(.CaseID)"' file1 \
  | awk -F ';' 'NR==FNR{array[$1]=$2; next} {print array[$1]}' - file2 \
  | sort -n

Output:

100
350
440

Answered By - Cyrus

Answer Checked By - Timothy Miller (WPSolving Admin)