Question 247255 in Json Question posted in
Our archive of expertly curated questions and answers provides insights and solutions to common problems related to this popular data interchange format. From parsing and manipulating JSON data to integrating it with various programming languages and web services, our archive has got you covered. Start exploring today and take your JSON skills to the next level

Merging Instana EUM JSONs with JQ in CMD

I’m using Instana to deliver view stats on my site, each daily file looks like this:

{
  "items" : [ {
    "name" : "page1.htm",
    "earliestTimestamp" : 1675222177839,
    "cursor" : {
      "@class" : ".IngestionOffsetCursor",
      "ingestionTime" : 1675292168217,
      "offset" : 1
    },
    "metrics" : {
      "uniqueSessions.distinct_count" : [ [ 1675292400000, 4.0 ] ]
    }
  }, {
    "name" : "page2.htm",
    "earliestTimestamp" : 1675260035165,
    "cursor" : {
      "@class" : ".IngestionOffsetCursor",
      "ingestionTime" : 1675292168217,
      "offset" : 2
    },
    "metrics" : {
      "uniqueSessions.distinct_count" : [ [ 1675292400000, 1.0 ] ]
    }
  }, {
    "name" : "page3.htm",
    "earliestTimestamp" : 1675228447118,
    "cursor" : {
      "@class" : ".IngestionOffsetCursor",
      "ingestionTime" : 1675292168217,
      "offset" : 3
    },
    "metrics" : {
      "uniqueSessions.distinct_count" : [ [ 1675292400000, 7.0 ] ]
    }
  } ],
  "canLoadMore" : false,
  "totalHits" : 12,
  "totalRepresentedItemCount" : 12,
  "totalRetainedItemCount" : 12,
  "adjustedTimeframe" : {
    "windowSize" : 86400000,
    "to" : 1675292400000
  }
}

These daily files should be merged into one json after filtering for the necessary info:

  • url (from name)

  • date (first value in "uniqueSessions.distinct_count")

  • number of page visits: (second value in "uniqueSessions.distinct_count")
    It is important, that it has to be done in CMD, since I have to use a batch file as the target user is not allowed to run PowerShell scripts, nor have access to any other CL tool.

So far, I managed to boil down the files to the needed data elements as separate JSON objects using: type *.json | jq ".items[] | {url: .name, date: .metrics[][0][0], load: .metrics[][0][1]}"

the result looks like:

{
  "url": "page1.htm",
  "date": 1675292400000,
  "load": 4
}
{
  "url": "page1.htm",
  "date": 1675292400000,
  "load": 1
}
{
  "url": "page1.htm",
  "date": 1675292400000,
  "load": 7
}

however, if I try to wrap it in square brackets (as tutorials suggest) to get a valid JSON, I get one file with a bunch of arrays starting and ending where they did in the original files.
I did the homework, and am aware of this: combining multiple json files into a single json file with jq filters actually, I played around with this for a while now before asking. I was thinking if I could add again curly brackets and a root node, it would help, but I haven’t found a way where JQ wouldn’t fail to do noting, that most probably the error comes from windows cmd’s quotation mark usage.

How can I make this into one JSON instead of as many arrays as many source files? Thanks!

Tags:

2

Answers


  1. 0

    For multiple input files, you can create another array around all of them using the --slurp (or -s option), then use map on that:

    jq -s 'map(.items[] | {…})' *.json

    Demo

    Or programmatically iterate (e.g.using reduce) over each input (using inputs in combination with the --null-input (or -n) flag):

    jq -n 'reduce inputs as {$items} ([]; . + [$items[] | {…}])' *.json

    Demo

    Login or Signup to reply.
  2. 0

    I’m sorry. I’m afraid I don’t know Instana nor JQ enough in order to exactly understand what you need… You have not show your desired final output file either… However, I do know Batch files enough!

    The pure Batch file below process all *.json files and extract your "needed data elements" as you show above. This is a first step to get the right solution, because this Batch file could be modified in any way you need.

    @echo off
setlocal

for %%f in (*.json) do (
   set "url="
   for /F "tokens=2,3 delims=[:,] " %%a in ('findstr "name uniqueSessions" "%%f"') do (
      if not defined url (
         echo "url": %%a
         set "url=%%a"
      ) else (
         echo "date": %%a
         echo "load": %%~Nb
         set "url="
      )
   )
)

    Output example:

    "url": "page1.htm"
"date": 1675292400000
"load": 4
"url": "page2.htm"
"date": 1675292400000
"load": 1
"url": "page3.htm"
"date": 1675292400000
"load": 7
"url": "page4.htm"
"date": 1675292400000
"load": 3
"url": "page5.htm"
"date": 1675292400000
"load": 6
"url": "page6.htm"
"date": 1675292400000
"load": 2

    Perhaps if you show us the desired output file, I could complete the solution

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search