how can i get a json object with curl without formatted new line, carriage return, tabs etc. just like a single line?

Question

I want to get a JSON object from the Google Direction API. The Google API response is formatted JSON with newlines like this:

{
  geocoded_waypoints: [
    {
      geocoder_status: "OK",
      place_id: "ChIJpws0Ra-DnkcRHLPUqU7qpiM",
      types: [
        "route"
      ]
    }
  ]
}

But I need the response in one single line, without formatting.

Can I do this with curl? I downloaded the newest version.

I try this yet:

1. this version put each line in a row in the database

   COPY "routing_import_gm" ("request") FROM PROGRAM 'curl "https://maps.googleapis.com/maps/api/directions/json?origin=48.xxxxxx,+11.yyyyyy&destination=48.xxxxxx,+11.yyyyyyy&key=***"';
   

2. cause erros:

   COPY "routing_import_gm" ("request") FROM PROGRAM 'curl "https://maps.googleapis.com/maps/api/directions/json?origin=48.xxxxxx,+11.yyyyyy&destination=48.xxxxxx,+11.yyyyyyy&key=***" | tr -d ''\n'' ';
   

Answer 1

The following PowerShell command loads JSON data in any format from a given URL and outputs a single line of JSON as its result:

Invoke-RestMethod "https://your/url" | ConvertTo-Json -Compress -Depth 10

• Invoke-RestMethod expects a JSON response, and decodes it into a data structure.
• ConvertTo-Json takes a data structure and turns it into JSON.
  • -Compress skips all indentation and all newlines,
  • -Depth sets the maximum nesting level (which is only 2 by default, not enough for your data).

You can run PowerShell with an in-line command like this:

powershell -NoLogo -NonInteractive -Command "..."

And you could use that in your COPY statement.

COPY "routing_import_gm" ("request")
FROM PROGRAM 'powershell -NoLogo -NonInteractive -Command "Invoke-RestMethod "https://your/url" | ConvertTo-Json -Compress -Depth 10"';

---

You say you have trouble with Unicode characters. That is a rather tricky problem in this setup. There are two issues:

• It requires proper configuration in multiple places to get Unicode characters across for shell commands. It's technically possible, but setting this up is a major pain.
• JSON offers an ASCII-compatible format, which encodes all Unicode characters as \u..... That would side-step the issue, but ConvertTo-JSON in PowerShell 5.1 (which you have) does not offer that natively. The -EscapeHandling parameter (which does that) requires PowerShell 6.2.

We rely on ConvertTo-JSON to format the incoming JSON as a single line, and we can't simply change your PowerShell version. A possible way out would be to replace all non-ASCII characters with \u.... character codes using regex:

[regex]::Replace($json, '[^\x00-\x7f]', {param($m) '\u{0:X4}'-f[int][char]$m[0].value})

If $json is '{"test": "Smørrebrød "}', the above returns '{"test": "Sm\u00F8rrebr\u00F8d \uD83E\uDD6A"}', which is both valid JSON and ASCII-only, so it won't give you any trouble with COPY FROM PROGRAM.

Applied to the code above, we would end up with this:

powershell -NoLogo -Command "$json = Invoke-RestMethod 'https://your/url' | ConvertTo-Json -Compress -Depth 10; [regex]::Replace($json, '[^\x00-\x7f]', {param($m) '\u{0:X4}'-f[int][char]$m[0].value})"

Notes

• While all of this is pretty convoluted, the advantage of using PowerShell is
  • All of it (requesting the URL, getting the JSON from the response, converting it) is Unicode-aware and never crosses process boundaries, as e.g. piping from curl for Windows into jq or sed or tr for Windows would. Crossing process boundaries always has a chance of mangling your Unicode data.
  • If the server itself returns literal Unicode characters in the JSON, they would get converted to \u.... as well.
  • PowerShell is readily available on every Windows machine, other tools must be installed first.
• If you can upgrade your PowerShell version, you could drop the regex part and use ConvertTo-JSON -EscapeHandling EscapeNonAscii instead.