How to extract a string based on a regex match from a unformatted file

Question

I have a unformatted file returned form a SQL query, from which I need to find a regex match and extract a numeric string that follows. The number is enclosed between two double quotes. Note there could be more then one occurrence of a match per line. There are other special characters in the file which should be ignored. Here is the example file:

{ "_id" : "66941672632817171654", "title" : "Some Name (Central)", "stationNameAssoc" : [ { "stationId" : "6248915749115539177", "stationName" : "Default" } ] }
{ "_id" : "4455677484649934117", "title" : "Some Name (Mountain)", "stationNameAssoc" : [ { "stationId" : "7597999415247634177", "stationName" : "Default" } ] }

From this, I want to extract a numeric after "stationId" excluding quotes e.g. 7597999415247634177. How would you do this using awk or bash? thanks

Answer 1

using a Json tool called json (https://github.com/trentm/json)

$ json -ga 'stationNameAssoc.[0].stationId' < input
6248915749115539177
7597999415247634177

or use it step by step:

cat input | json -ga 'stationNameAssoc' | json -ga stationId

documentation in: http://trentm.com/json/

---

if not installed:

install node
and sudo npm install -g json

Answer 2

Another JSON parser solution, using jq:

$ jq -r '.stationNameAssoc[0].stationId' data.json
6248915749115539177
7597999415247634177

It simple fetches the values of the stationId entry of the first (and only) array item in each stationNameAssoc array.

Answer 3

awk -F\" '{for(i=1;i<=NF;i++)if($i~/Id/){print $(i+2);next}}' input.txt