Skip to content
Connect > Data APIs > Use Cases

Query a table to JSON

Goal: pull a Center table out from an external script and work with it as data: a curl check, then a Python example that loads the result into a pandas DataFrame for ad-hoc analysis.

This is the everyday analyst path: positions, fills, or quotes in your notebook without exporting anything by hand.

Prerequisites

  • The REST QUERY endpoint is enabled (ami.rest.plugin.classes=*): see Configuration.
  • A table to read. This example uses QUOTES (SYM, bid, ask, last, volume); the Quickstart creates it.

1. Check it from the command line

Ask for jsonMaps: the format that returns each table as a list of row objects, which is the easiest to consume:

1
2
3
curl -sk -u username:password -G \
  --data-urlencode "cmd=SELECT SYM, bid, ask, last FROM QUOTES" \
  "https://localhost:33333/3forge_rest/query?display=jsonMaps"

The response looks like:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
{
  "status": "ok",
  "durationNanos": 512000,
  "rowsAffected": 0,
  "tables": {
    "QUOTES": [
      {"SYM": "IBM", "bid": 123.40, "ask": 123.50, "last": 123.45}
    ]
  }
}

The rows are under tables.<title>; the title matches the table (or the query's output name). With jsonMaps, tables is an object you index by name (tables["QUOTES"]), and the keys inside each row are alphabetized, not in SELECT order.

2. Load it into pandas

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
import requests
import pandas as pd

resp = requests.get(
    "https://localhost:33333/3forge_rest/query",
    params={"cmd": "SELECT SYM, bid, ask, last, volume FROM QUOTES", "display": "jsonMaps"},
    auth=("username", "password"),
    verify=False,   # accept a self-signed dev cert; use proper certs in production
)
resp.raise_for_status()
body = resp.json()

if body["status"] != "ok":
    raise RuntimeError(body.get("error", {}).get("message", "query failed"))

rows = body["tables"]["QUOTES"]
df = pd.DataFrame(rows)
print(df)

df is now a normal DataFrame: filter, aggregate, join with other sources, or write it to a file. Because this is a plain GET, the same call works from any language or from a scheduled job that pulls an end-of-day extract.

Tips

  • Narrow the query, not the client. Push filtering and aggregation into cmd (WHERE, GROUP BY) so you transfer only what you need; raise limit only when you truly want more than 10,000 rows.
  • Pick the format for the consumer, and mind the container. jsonMaps returns tables as an object keyed by table name; jsonRows and json return tables as an array of table objects. Switching format means changing how you reach the rows (body["tables"]["QUOTES"] vs body["tables"][0]). Use jsonRows/json when you need columns in declared order (jsonMaps alphabetizes keys); pipe for a quick shell split.
  • This is a point-in-time pull. If you need to keep watching the table as it changes, use the WebSocket streaming API instead.