Using $ variables in jq from PowerShell

Question

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.

Answer 1

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

<sup>* 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.</sup>

---

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