JSON – How to Extract and Print Key-Value Pairs Matching a Pattern

jqjson

JSON:

"{
  "a": "https://is2-ssl.com/",
  "b": "https://a5.-ssl.com/",
  "type": "response",
  "c": [
    {
      "Number": 1,
      "Reportname": "XXX",
      "size": "2.5",
      "Variants": [
        "YYY"
      ]
    }
  ],
"Meta": "ABC"
}

Required Output:

XXX,a,https://is2-ssl.com/
XXX,b,https://a5.-ssl.com/

I want to print those key value pairs in which the value contains "http"(url) and merge the key and value with delimiter "," and add ReportName to them

Best Answer

Assuming the initial " in the JSON document is a typo, that you want the .ReportName key's value from the first entry in the c array, and that you want the output as CSV:

$ jq -r '.c[0].Reportname as $n | map_values(select(type == "string" and startswith("http"))) | to_entries[] | [$n, .key, .value] | @csv' file
"XXX","a","https://is2-ssl.com/"
"XXX","b","https://a5.-ssl.com/"

The jq expression:

.c[0].Reportname as $n |
map_values(select(type == "string" and startswith("http"))) |
to_entries[] | [$n, .key, .value] | @csv

This first picks out the .Reportname value and assigns it to the internal jq variable $n. It then rewrites the original object by discarding any key not associated with a string starting with the substring http.

The example document is, after map_values() reduced to

{
  "a": "https://is2-ssl.com/",
  "b": "https://a5.-ssl.com/"
}

This is then converted with to_entries into the equivalent of

[
   { "key": "a", "value": "https://is2-ssl.com/" },
   { "key": "b", "value": "https://a5.-ssl.com/" }
]

... to get access to the keys themselves.

This array is expanded into a set of objects with [] and the saved value in $n together with the .key and .value part of each object in the set is used to create an array that is converted by @csv to quoted CSV output.

Another jq expression with the same output, but that does to_entries a bit earlier and then does map() on the created array rather than on the values of an object as we do with map_values() above.

.c[0].Reportname as $n | 
to_entries | 
map(
   select(.value | type == "string" and startswith("http")) |
   [$n, .key, .value] | @csv
)[]
Related Question