Output semicolon-separated string

Question

Lets say we have this file:

{
  "persons": [
    {
      "friends": 4,
      "phoneNumber": 123456,
      "personID": 11111
    },
    {
      "friends": 2057,
      "phoneNumber": 432100,
      "personID": 22222
    },
    {
      "friends": 50,
      "phoneNumber": 147258,
      "personID": 55555
    }
  ]
}

I now want to extract the phone numbers of the persons 11111, 22222, 33333, 44444 and 55555 as a semicolon-separated string:

123456;432100;;;147258

While running

cat persons.txt | jq ".persons[] | select(.personID==) | .phoneNumber"

once for each and glueing the results together with the ; afterwards works, this is terribly slow, because it has to reload the file for each of the IDs (and other fields I want to extract).

Concatenating it in a single query:

 cat persons.txt | jq "(.persons[] | select(.personID==11111) | .phoneNumber), (.persons[] | select(.personID==22222) | .phoneNumber), (.persons[] | select(.personID==33333) | .phoneNumber), (.persons[] | select(.personID==44444) | .phoneNumber), (.persons[] | select(.personID==55555) | .phoneNumber)"

This also works, but it gives

123456
432100
147258

so I do not know which of the fields are missing and how many ; I have to insert.

user_997358 · Answer

With your sample input in input.json, and using jq 1.6 (or a jq with INDEX/2), the following invocation of jq produces the desired output:

jq -r --argjson ids '[11111, 22222, 33333, 44444, 55555]' -f tossv.jq input.json

assuming tossv.jq contains the program:

INDEX(.persons[]; .personID) as $dict
| $ids
| map( $dict[tostring] | .phoneNumber)
| join(";")

Program notes

INDEX/2 produces a JSON object that serves as a dictionary. Since JSON keys must be strings, tostring must be used in line 3 above.
When using join(";"), null values effectively become empty strings.
If your jq does not have INDEX/2, then now might be a good time to upgrade. Otherwise you can snarf its definition by googling: jq "def INDEX" builtin.jq

user_6403504 · Answer

Unfortunately I couldn't test if peak's answer works since I only have jq 1.5. Here's what I came up with yesterday evening:

For each semicolon, add the following query
```
(";" as \$a | \$a)
```

Resulting command (abstract):

 cat persons.txt | jq "(<1's phone number>), (";" as \$a | \$a), 
 (<2's phone number>), (";" as \$a | \$a), ..."

Resulting command (concrete):

cat persons.txt | jq "(.persons[] | select(.personID==11111) | .phoneNumber), (";" as \$a | \$a), 
(.persons[] | select(.personID==22222) | .phoneNumber), (";" as \$a | \$a), 
(.persons[] | select(.personID==33333) | .phoneNumber), (";" as \$a | \$a), 
(.persons[] | select(.personID==44444) | .phoneNumber), (";" as \$a | \$a), 
(.persons[] | select(.personID==55555) | .phoneNumber)"

Result:
```
123456
";"
432100
";"
";"
";"
147258
```
Delete the newlines and ":
```
 | tr --delete "
""
```
Result:
```
123456;432100;;;147258
```

Do not get me wrong, this is far uglier than peak's answer, but it worked for me yesterday.

user_10530633 · Answer

Without jq solution:

for i in $(seq 11111 11111 55555)
do
  string=$(grep -B1 "$i" persons.txt | head -1 | sed 's/.* $.*$,/\1/g')
  echo "$string;" >> output
done
cat output | tr -d '\n' | rev | cut -d';' -f2- | rev > tmp && mv tmp output

This little script will yield the result you want and you can adapt it quickly if the input data varies

cat output
123456;432100;;;147258

Output semicolon-separated string

3 Answers

Program notes