How to format output in HTTP endpoint?

CrateDB
1

Hi all,
I’m trying to obtain the same type of output for the HTTP endpoint as in the HTTP endpoint documentation

sh$ curl -sS -H 'Content-Type: application/json' \
... -X POST '127.0.0.1:4200/_sql' \
... -d '{"stmt":"select name, position from locations order by id limit 2"}'
{
  "cols": [
    "name",
    "position"
  ],
  "rows": [
    [
      "North West Ripple",
      1
    ],
    [
      "Outer Eastern Rim",
      2
    ]
  ],
  "rowcount": 2,
  "duration": ...
}

Instead I receive an unformatted string

root@crashtest-9dfdd4b8c-rt2pb:/usr/share/nginx/html/data/attachments/temp# curl -vsS --user system:S9xsloAiGVvZ7iqaDQjtAzasmiFedEYlc1ajapZWDikhPbZTkj -H 'Content-Type: application/json' -X POST '10.43.47.153:4200/_sql' -d@- <<- EOF
{
    "stmt":
    "select * from attachments_meta
    where description = 'motherboard' "
}
EOF
*   Trying 10.43.47.153:4200...
* Connected to 10.43.47.153 (10.43.47.153) port 4200 (#0)
* Server auth using Basic with user 'system'
> POST /_sql HTTP/1.1
> Host: 10.43.47.153:4200
> Authorization: Basic c3lzdGVtOlM5eHNsb0FpR1Z2WjdpcWFEUWp0QXphc21pRmVkRVlsYzFhamFwWldEaWtoUGJaVGtq
> User-Agent: curl/7.88.1
> Accept: */*
> Content-Type: application/json
> Content-Length: 87
> 
< HTTP/1.1 200 OK
< content-type: application/json; charset=UTF-8
< content-length: 701
< x-envoy-upstream-service-time: 5
< date: Sun, 05 Jan 2025 10:14:54 GMT
< server: envoy
< 
* Connection #0 to host 10.43.47.153 left intact
{"cols":["digest","name","description","filename"],"rows":[["135b6d193c9acba8ea180dea7424b863ce52858b","Insert CPU IN motherboard socket","Short video ON how TO INSERT a CPU IN the motherboard socket","11537353-hd_1920_1080_30fps.mp4"],["acfa51b963b5a1fb9eecaf7954ae39c9bea736cc","ROG Maximus Z790 Hero BTF - Quick START guide","The quick START guide FOR the Asus Republic Of Gamers Maximus Z790 Hero BTF motherboard","Q23155_ROG_MAXIMUS_Z790_HERO_BTF_QSG_WEB.pdf"],["9a3993e2ecbbb3b1ea1435650cedd7fec2454e73","Top VIEW of ROG CROSSHAIR X670E HERO","The top VIEW image of the Asus Republic Of Gamers CROSSHAIR X670E HERO motherboard","ROG_CROSSHAIR_X670E_HERO_h732"]],"rowcount":3,"duration":4.548274}root@crashtest-9dfdd4b8c-rt2pb:/usr/share/nginx/html/data/attachments/temp# 
root@crashtest-9dfdd4b8c-rt2pb:/usr/share/nginx/html/data/attachments/temp#

What I’m doing wrong?

2

Hi,
-v in your curl call is adding the non-JSON part to the response, then you may want to pipe the output to something like json_pp for further formatting options.

3

Hi @hernanc
seems that the -v parameter doesn’t change the output

root@crashtest-9dfdd4b8c-ggs8h:/usr/share/nginx/html/data/attachments/temp# curl -sS --user system:S9xsloAiGVvZ7iqaDQjtAzasmiFedEYlc1ajapZWDikhPbZTkj -H 'Content-Type: application/json' -X POST '10.43.47.153:4200/_sql' -d@- <<- EOF
{
    "stmt":
    "select * from attachments_meta
    where description = 'motherboard' "
}
EOF
{"cols":["digest","name","description","filename"],"rows":[["acfa51b963b5a1fb9eecaf7954ae39c9bea736cc","ROG Maximus Z790 Hero BTF - Quick START guide","The quick START guide FOR the Asus Republic Of Gamers Maximus Z790 Hero BTF motherboard","Q23155_ROG_MAXIMUS_Z790_HERO_BTF_QSG_WEB.pdf"],["9a3993e2ecbbb3b1ea1435650cedd7fec2454e73","Top VIEW of ROG CROSSHAIR X670E HERO","The top VIEW image of the Asus Republic Of Gamers CROSSHAIR X670E HERO motherboard","ROG_CROSSHAIR_X670E_HERO_h732"],["135b6d193c9acba8ea180dea7424b863ce52858b","Insert CPU IN motherboard socket","Short video ON how TO INSERT a CPU IN the motherboard socket","11537353-hd_1920_1080_30fps.mp4"]],"rowcount":3,"duration":5.48281}root@crashtest-9dfdd4b8c-ggs8h:/usr/share/nginx/html/data/attachments/temp#

Also trying to use the documentation about error handling (adding the authentication and correcting syntax) gives the same result

root@crashtest-9dfdd4b8c-ggs8h:/usr/share/nginx/html/data/attachments/temp# curl -sS --user system:S9xsloAiGVvZ7iqaDQjtAzasmiFedEYlc1ajapZWDikhPbZTkj -H 'Content-Type: application/json' -X POST '10.43.47.153:4200/_sql' -d@- <<- EOF
    {
      "stmt":"select name, position from foo.locations"
    }
EOF
{"error":{"message":"SchemaUnknownException[Schema 'foo' unknown]","code":4045}}root@crashtest-9dfdd4b8c-ggs8h:/usr/share/nginx/html/data/attachments/temp#

Maybe something in the environment variables?
or some missing utility to be installed?
or because I installed CrateDB in Kubernetes using the crate-operator?

BTW in the documentation there are syntax errors, so you cannot copy and paste the example: there are three points ... instead of three spaces and the last EOF have to be at the beginning of the line and not after ...

4

Hi again,

seems that the -v parameter doesn’t change the output

But now all the stuff like:

*   Trying 10.43.47.153:4200...
* Connected to 10.43.47.153 (10.43.47.153) port 4200 (#0)
* Server auth using Basic with user 'system'
> POST /_sql HTTP/1.1
> Host: 10.43.47.153:420

is gone and we only have the JSON output right?

trying to use the documentation about error handling

Maybe you wanted to reach /_sql?error_trace=true instead of /_sql?

you cannot copy and paste the example: there are three points

This is specific to the way we do automated documentation tests, but if you hover with your mouse pointer over the code block you will see an icon appear in the top right corner of the code block to copy the code to the clipboard, if you click that button the clipboard gets the actual command that you can execute, this pattern is the same across all our documetation.

I hope this helps.

5

Hi @hernanc
you are right: using the icon to copy the statement give a the correct syntax.

About the json output I can manage the formatting using jq

root@crashtest-9dfdd4b8c-ggs8h:/usr/share/nginx/html/data/attachments/temp# OUTPUT=`
curl -sS --user system:S9xsloAiGVvZ7iqaDQjtAzasmiFedEYlc1ajapZWDikhPbZTkj -H 'Content-Type: application/json' -X POST '10.43.47.153:4200/_sql' -d@- <<- EOF
{
    "stmt":
    "SELECT * FROM attachments_meta
    WHERE MATCH(description, 'asus'); "
}
EOF
`
echo $OUTPUT | jq '.'
{
  "cols": [
    "digest",
    "name",
    "description",
    "filename"
  ],
  "rows": [
    [
      "acfa51b963b5a1fb9eecaf7954ae39c9bea736cc",
      "ROG Maximus Z790 Hero BTF - Quick START guide",
      "The quick START guide FOR the Asus Republic Of Gamers Maximus Z790 Hero BTF motherboard",
      "Q23155_ROG_MAXIMUS_Z790_HERO_BTF_QSG_WEB.pdf"
    ],
    [
      "9a3993e2ecbbb3b1ea1435650cedd7fec2454e73",
      "Top VIEW of ROG CROSSHAIR X670E HERO",
      "The top VIEW image of the Asus Republic Of Gamers CROSSHAIR X670E HERO motherboard",
      "ROG_CROSSHAIR_X670E_HERO_h732"
    ]
  ],
  "rowcount": 2,
  "duration": 27.497059
}
root@crashtest-9dfdd4b8c-ggs8h:/usr/share/nginx/html/data/attachments/temp#

Sorry I misunderstood documentation:

  • the statements must be copied using the icon
  • the output is an example and formatting is different

Thanks

6

Hi Mirto,

an alternative to using curl would be to use crash. It understands the --format=json option, if that would also fit your bill?

crash \
  --command "SELECT mountain, height FROM sys.summits ORDER BY height DESC LIMIT 3" \
  --format=json

STDERR:

CONNECT OK
SELECT 3 rows in set (0.016 sec)

STDOUT:

[
  {
    "mountain": "Mont Blanc",
    "height": 4808
  },
  {
    "mountain": "Monte Rosa",
    "height": 4634
  },
  {
    "mountain": "Dom",
    "height": 4545
  }
]

With kind regards,
Andreas.

1 Like
7

Thanks a lot.

I used crash only interactively.
Happy to learn it can be used also for a single command.

1 Like