$dump-sql
Dump results of the sql query
$dump-sql operation takes the sql query and responds with the Chunked Encoded stream in TSV format (default) or in JSON/NDJSON format. Useful to export data for analytics.
$dump-sql is an Aidbox-only endpoint. It is available at /$dump-sql and is not available at /fhir/$dump-sql. Requests to the /fhir/ prefixed path will return a 404 error.
Streaming and errors: The response is streamed. If the SQL is invalid or the request body is missing or malformed, the server may still return HTTP 200 and write error text into the response body instead of returning 4xx with an OperationOutcome. Check the response body for error messages when the output is unexpected.
POST [base]/$dump-sql
Headers
| Parameter | Required? | Type | Description |
|---|---|---|---|
| Parameter | Required? | Type | Description |
| content-type | true | String | Content-type of the query body |
Query parameters
| Parameter | Required? | Type | Description |
|---|---|---|---|
| Parameter | Required? | Type | Description |
| _format | false | String |
|
Body parameters
| Parameter | Required? | Type | Description |
|---|---|---|---|
| Parameter | Required? | Type | Description |
| query | true | String | Sql query to execute |
Example
Get id and name of each patient
REST Console
POST /$dump-sql
Content-Type: text/yaml
query: select id, resource#>>'{name,0,given,0}' from patient
Curl
curl -u bulk-client:secret $AIDBOX_BASE_URL/\$dump-sql \
-H 'Content-Type: text/yaml' -d@- <<EOF
query: select id, resource#>>'{name,0,given,0}' from patient
EOF
Status
200 OK
Headers
| Header | Value |
|---|---|
| Content-Type | text/tab-separated-values |
| Transfer-Encoding | Chunked |
Body
pt-1 Alice
pt-2 Bob
pt-3 Charles
Body as table
| pt-1 | Alice |
| pt-2 | Bob |