This topic describes how to create and modify tables in flash application scenario, and read and write data using standard SQL. The example Collection name my_flash_app and app name app001 are consistent with those in Quick start with flash application scenario.
Note
This feature is supported starting from V4.6.2.1.
After connecting to the client and creating a Collection, you can operate on data as if it were a regular table: define the table structure first, then execute INSERT, UPDATE, DELETE, or SELECT. Write the business table name directly in the SQL (for example, users), and json-table-sdk locates the data under the current app, without requiring knowledge of the underlying JSON storage.
Specify an app
Each time you create a table or perform read/write operations, you must pass a RequestContext to specify under which app the operation is being performed. It can also carry audit, tracking, and other information.
from antob_json_table import RequestContext
ctx = RequestContext(
coll,
app_prefix="app001",
created_by="admin",
)
Example with optional parameters:
ctx = RequestContext(
coll,
app_prefix="app001",
version="v1.0",
created_by="admin",
trace_id="trace-123",
extra={"key": "value"},
)
Parameters |
Required |
Note |
|---|---|---|
coll |
Yes | Collection object, returned by create_collection, get_collection, or get_or_create_collection. |
app_prefix |
Yes | Application identifier that maps to a Namespace name. Data of different app_prefix values in the same Collection is isolated from each other. For details, see Collection and app management. |
created_by |
No | Operator. Defaults to admin and is used for auditing. |
version |
No | User-defined version number. |
trace_id |
No | The trace ID is used for log correlation. |
extra |
No | An optional field of type dictionary for users to pass additional information as needed. |
Write the business table name directly in the SQL (for example, users), and the SDK locates the data based on the Namespace corresponding to the current app_prefix and the table name.
Create and modify tables
Common methods are as follows:
Method |
Note |
|---|---|
create_table(ctx, sql) |
Creates a table (virtual table). Only the table schema is registered; no separate physical table is created for each table. |
alter_table_add_column(ctx, sql) |
Adds a column to an existing table. The new column must be specified with DEFAULT. Existing rows are populated with this default value. |
get_table_schema(ctx, table_name) |
View the table structure. |
list_tables(ctx) |
Lists the table names under the current app. |
table_exists(ctx, table_name) |
Determines whether the table exists. |
drop_table(ctx, table_name) |
Drop a table and delete all data in it. |
Create a table
client.create_table(ctx, """
CREATE TABLE users (
id BIGINT NOT NULL,
name VARCHAR(64) NOT NULL,
age INT DEFAULT 0,
created_at TIMESTAMP
)
""")
When calling create_table for the first time, if the Namespace corresponding to the current app_prefix does not exist, the system will automatically create it.
Considerations:
- Each app can have up to 100,000 user tables; see Limitations and constraints for syntax boundaries.
- A single table can have at most 100 columns; a single row serialized as JSON cannot exceed 64 KB;
VARCHARcan be up to 65,535 characters long. - Common column types:
TINYINT,SMALLINT,INT,BIGINT,DECIMAL,FLOAT,DOUBLE,VARCHAR(n),DATE,TIME,TIMESTAMP.
Add a column
The DEFAULT value must be specified for the new column:
# Correct
client.alter_table_add_column(
ctx,
"ALTER TABLE users ADD COLUMN email VARCHAR(128) DEFAULT ''",
)
# Error: DEFAULT is missing. A ValidationError will be thrown.
# client.alter_table_add_column(ctx, "ALTER TABLE users ADD COLUMN email VARCHAR(128) NOT NULL")
View and delete tables
tables = client.list_tables(ctx)
exists = client.table_exists(ctx, "users")
schema = client.get_table_schema(ctx, "users")
client.drop_table(ctx, "users")
Write, update, and delete
Method |
Note |
|---|---|
insert(ctx, sql) |
Insert one or more rows. |
update(ctx, sql) |
Updates based on conditions. |
delete(ctx, sql) |
Delete by condition. |
json-table-sdk automatically limits operations to the current app and table, preventing accidental modification of data in other apps.
# Column name must be specified
client.insert(ctx, "INSERT INTO users (id, name, age) VALUES (1, 'Zhang San', 25)")
client.insert(ctx, """
INSERT INTO users (id, name, age) VALUES
(1, 'John Doe', 25),
(2, 'Li Si', 30)
""")
client.update(ctx, "UPDATE users SET age = 26 WHERE id = 1 LIMIT 10")
client.delete(ctx, "DELETE FROM users WHERE id = 2 LIMIT 10")
When writing data, note the following:
- A single
INSERTstatement can insert at most 1,000 rows, and column names must be specified. For larger batches, divide the data into smaller chunks (recommended size: 100–200 rows per batch). - It is recommended to use
LIMITforUPDATEandDELETEstatements. - For unsupported
INSERTsyntax, see Limitations and constraints.
Query
Method |
Note |
|---|---|
select(ctx, sql) |
Executes a query and returns structured results. |
translate(ctx, sql) |
The statements are translated only for debugging purposes and are not actually executed. |
Here is an example:
# Query All Data
rows = client.select(ctx, "SELECT * FROM users")
# Conditional Query
rows = client.select(
ctx,
"SELECT * FROM users WHERE age > 25 ORDER BY age DESC LIMIT 100",
)
# Example of a self-join on the same table
rows = client.select(ctx, """
SELECT a.id AS emp_id, b.id AS mgr_id
FROM users a INNER JOIN users b ON a.manager_id = b.id
LIMIT 100
""")
When querying, note the following:
- A single
LIMITclause can return at most 5,000 rows;ORDER BYcan involve at most 3 columns;GROUP BYcan involve at most 5 columns. - Supported and unsupported syntax is described in Limitations and constraints.