CODEWITHSUNDEEP
jsonpowershelljq
kobuta23
kobuta23

Reputation: 33

Using $ variables in jq from PowerShell

I am using this code:

jq --raw-output "(map(keys) | add | unique) as $cols | map(. as $row | $cols | map($row[.])) as $rows | $cols, $rows[] | @csv" output2.json > output3.csv

to create CSV from my JSON database:

[
  {
    "key1": "field1",
    "key2": "field2",
    "key3": "field3"
  },
  {
    "key1": "field4",
    "key2": "field5",
    "key3": "field6"
  }
]

The output is perfect on https://jqplay.org/, but doesn't seem to work at all when running on my command line. I use PowerShell inside VSCode.

I keep getting this error.

jq: error: syntax error, unexpected '|', expecting '$' or '[' or '{' (Windows cmd shell quoting issues?) at , 
line 1: (map(keys) | add | unique) as  | map(. as  |  | map([.])) as  | , [] | @csv
jq: 1 compile error

I guessed that the $ were the problem as they are not shown in the error text, so I tried escaping them in various ways but made no progress. I have searched extensively and no-one seems to have this problem, so I must be getting something very simple very wrong.

Upvotes: 3

Views: 15320

Answers (2)

mklement0
mklement0

Reputation: 439477

Your jq script:

  • must be passed as a literal PowerShell string ('...'),
  • because an interpolating PowerShell string ("...") by design interprets $-prefixed tokens as PowerShell variable references; e.g., $cols (or PowerShell subexpressions ($(...)), which doesn't apply in this case):
jq -r '(map(keys) | add | unique) as $cols | 
       map(. as $row | $cols | map($row[.])) as $rows | 
         $cols, $rows[] | @csv' output2.json > output3.csv

* I've added line breaks for readability. PowerShell string literals can span multiple lines, so you should be able to paste the multi-line command as-is. Of course, you can format it as a single line.
* -r ensures that the results are output "raw", i.e., without JSON value escaping.
* If you save a jq script to a file to be used with the -f option, be sure to save it as UTF-8 without a BOM.

Here's a simplified example that demonstrates the difference:

# OK: SINGLE-quoted jq script:
PS> '{ "foo": "bar" }' | jq '. as $cols | .foo'
"bar"

# BROKEN: DOUBLE-quoted jq script: PowerShell expands `$cols` up front, 
#         resulting in an empty string, if no such PowerShell variable exists. 
#         jq then effectively sees '. as  | .foo', which explains the syntax error.
PS> '{ "foo": "bar" }' | jq ". as $cols | .foo"
jq: error: syntax error, unexpected '|', expecting '$' or '[' or '{' (Unix shell quoting issues?) at <top-level>, line 1:
. as  | .foo      
jq: 1 compile error

Upvotes: 7

jq170727
jq170727

Reputation: 14695

For reference, when I put your (slightly formatted) filter in a file called filter.jq

  (map(keys) | add | unique) as $cols
| map(. as $row | $cols | map($row[.])) as $rows
| $cols, $rows[]
| @csv

and your data in a file called json.data and run

jq -r -f filter.jq json.data

I see the following output

"key1","key2","key3"
"field1","field2","field3"
"field4","field5","field6"

So the problem is likely in the exact command you're running. Without seeing that it's difficult to say but putting your filter in a separate file would probably prevent any command-line quoting/escaping problems.

Upvotes: 3

Related Questions