I have some JSON data and i want to push some of them to DB , but sometimes the json values not exists for specific devices: from all of the following data I want just to pull , "ICCID","MDN","MSISDN","MCC","MNC","FeatureTypes","RatePlanCode","RatePlanDescription","DeviceState","BillingCycleStartDate","BillingCycleEndDate","CurrentBillCycleDataUnRatedUsage"

and if any one not exist print not-exist .

{
"categories": [{
        "categoryName": "DeviceIdentifier",
        "extendedAttributes": [{
                "key": "ICCID",
                "value": "89148000"
            },
            {
                "key": "IMSI",
                "value": "31148094"
            },
            {
                "key": "MDN",
                "value": "5514048068"
            },
            {
                "key": "MEID",
                "value": "A0000000005006"
            },
            {
                "key": "MIN",
                "value": "5514041185"
            }
        ]
    },
    {
        "categoryName": "DeviceAttributes",
        "extendedAttributes": [{
                "key": "MCC",
                "value": "311"
            },
            {
                "key": "MNC",
                "value": "480"
            },
            {
                "key": "FeatureCodes",
                "value": "75802,84777,54307"
            },
            {
                "key": "FeatureNames",
                "value": "75802,84777,54307"
            },
            {
                "key": "FeatureTypes",
                "value": "4G Public Dynamic"
            },
            {
                "key": "RatePlanCode",
                "value": "4G5G "
            },
            {
                "key": "RatePlanDescription",
                "value": "4G5G"
            },
            {
                "key": "Services",
                "value": "null"
            }
        ]
    },
    {
        "categoryName": "Provisioning",
        "extendedAttributes": [{
                "key": "LastActivationDate",
                "value": "2022-03-01T19:38:52Z"
            },
            {
                "key": "CreatedAt",
                "value": "2021-12-01T21:22:55Z"
            },
            {
                "key": "DeviceState",
                "value": "active"
            },
            {
                "key": "LastDeactivationDate",
                "value": "2021-12-01T21:22:55Z"
            }
        ]
    },
    {
        "categoryName": "Connectivity",
        "extendedAttributes": [{
                "key": "Connected",
                "value": "true"
            },
            {
                "key": "LastConnectionDate",
                "value": "2022-09-08T03:38:55Z"
            },
            {
                "key": "LastDisconnectDate",
                "value": "2022-09-08T03:25:15Z"
            }
        ]
    },
    {
        "categoryName": "Billing",
        "extendedAttributes": [{
                "key": "BillingCycleStartDate",
                "value": "2022-09-02T00:00:00Z"
            },
            {
                "key": "BillingCycleEndDate",
                "value": "2022-10-01T00:00:00Z"
            },
            {
                "key": "DefaultRatePlan",
                "value": "0"
            }
        ]
    },
    {
        "categoryName": "Usage",
        "extendedAttributes": [{
                "key": "CurrentRatedUsageRecordDate",
                "value": "2022-09-04T00:00:00Z"
            }, {
                "key": "CurrentUnRatedUsageRecordDate",
                "value": "2022-09-08T01:25:15Z"
            },
            {
                "key": "CurrentBillCycleDataUnRatedUsage",
                "value": "1698414605"
            }
        ]
    }

]

}

i'm not pushing all fields to db so i'm selecting a specific keys from that, (what i'am selecting its fixed not changed) so the select will not change and always will be :

Expected output :

"89148000" "5514048068" "not-exist" "4G Public Dynamic" "4G5G" "4G5G" "active" "2022-09-02T00:00:00Z" "2022-10-01T00:00:00Z" "2022-09-08T01:25:15Z"

I would like to check if the value of key is missing for this case "MSISDN" ,if not will print for me not-exist or null

any help ?

CodePudding user response：

.categories[].Attributes[] | 
    if (.key | IN(["AAA","BBB","DDD","EEE"][])) 
        then .value 
        else "NOT-EXIST" 
    end

Gives the following output

"111"
"222"
"NOT-EXIST"
"444"
"555"

• First we loop over the Attributes
• Then we use an if to;
  • Check if key exist in ["AAA","BBB","DDD","EEE"]
  • TRUE: use .value
  • FALSE: use NOT-EXIST as value

Demo

Another approach, using with_entries() to update the .value before looping over all the objects to show just the value, gives the same output as above:

.categories[].Attributes[] 
    | select(.key | IN(["AAA","BBB","DDD","EEE"][]) | not).value = "NOT-EXIST"
    | .value

Demo

CodePudding user response：

I hope I understood your requirements correctly, but here is a solution that looks simple enough to understand and should be somewhat efficient. If you always expect the same 5 keys in your input, you can try:

.categories[].Attributes | from_entries as $obj
  | ["AAA", "BBB", "CCC", "DDD", "EEE"]
  | map($obj[.] // "NOT-EXIST")

Input:

{"categories": [
{
  "categoryName": "Device",
  "Attributes": [
    {
      "key": "AAA",
      "value": "111"
    },
    {
      "key": "BBB",
      "value": "222"
    },
    {
      "key": "DDD",
      "value": "444"
    },
    {
      "key": "EEE",
      "value": "555"
    }
  ]
}]}

Output:

[
  "111",
  "222",
  "NOT-EXIST",
  "444",
  "555"
]

If you require only the values, add [] or | .[] at the end of the script or rewrite to:

.categories[].Attributes | from_entries as $obj
  | "AAA", "BBB", "CCC", "DDD", "EEE"
  | $obj[.] // "NOT-EXIST"

With the input from updated question, you intend to first merge all extendedAttributes array into one big array, convert to an object and then use this complete object to look up your values:

.categories | map(.extendedAttributes[]) | from_entries as $obj
  | "ICCID", "MDN", "MSISDN", "MCC", "MNC", "FeatureTypes", "RatePlanCode", "RatePlanDescription", "DeviceState", "BillingCycleStartDate", "BillingCycleEndDate", "CurrentBillCycleDataUnRatedUsage"
  | $obj[.] // "NOT-EXIST"

.categories | map(.extendedAttributes[]) can be rewritten as [.categories[].extendedAttributes[]] or .categories | map(.extendedAttributes) | add, which might be easier to grok.

Output:

"89148000"
"5514048068"
"NOT-EXIST"
"311"
"480"
"4G Public Dynamic"
"4G5G "
"4G5G"
"active"
"2022-09-02T00:00:00Z"
"2022-10-01T00:00:00Z"
"1698414605"