What This API Solves

The Jushuitan /open/inventory/query API pulls product inventory from Jushuitan into a local MySQL table jst_inventory_query. It supports multi-warehouse aggregation, inventory threshold alerts, sellable-stock calculation, and integration with ERP/BI systems. In our supply-chain integration projects, almost every customer needs this link.

API Capability Overview

Authentication: Standard Jushuitan Open Platform mechanism using partnerId + signature; AppKey, timestamp, and signature are passed in request headers.
Request Structure: POST with form/query parameters; the core parameters are wms_co_id, modified_begin, modified_end, page_index, page_size, and sku_ids.
Response Structure: JSON containing a datas array; each entry represents one inventory record, and the top-level has_next flag indicates whether more pages exist.
Pagination: page_index starts from 1; page_size defaults to 30 with a maximum of 50. Loop until has_next=false.
Incremental Mode: Incremental pulls are based on the modified field; the window between modified_begin and modified_end must not exceed 7 days, otherwise the API returns an error.
Special Queries: When wms_co_id is omitted or set to 0, the response returns total stock across all warehouses. sku_ids accepts up to 20 SKUs and cannot be empty together with the modification-time window.

Typical Field Mapping

Field	Type	Meaning	Practical Notes
i_id	string	Inventory record primary key	Recommend a composite key `{{sku_id}}-{{wms_co_id}}-{{ts}}` in MySQL to avoid cross-warehouse conflicts
sku_id	string	SKU code	`number` in metadata points to this field; used as business key
name	string	Product name	Paired with sku_id for human identification
qty	string	Available stock	Real sellable quantity, often combined with virtual/lock fields
virtual_qty	string	Virtual stock	Virtual additions such as presales and reservations
purchase_qty	string	In-transit purchases	Purchase orders not yet received
allocate_qty	string	Allocated quantity	Allocated but not yet shipped
order_lock	string	Order lock	Reserved by orders; reduces sellable
pick_lock	string	Pick lock	Locked during picking
in_qty	string	Inbound in-transit	Inbound not yet completed
return_qty	string	Return in-transit	Returns not yet received
defective_qty	string	Defective	Non-sellable stock
min_qty / max_qty	string	Threshold values	Used for low/over-stock alerts
modified	string	Modified time	Key field for incremental pull; rely on the server time
ts	string	Timestamp	Sync timestamp, often used as an idempotency helper key

A common empirical formula for sellable stock: qty + virtual_qty - order_lock - pick_lock - allocate_qty. Confirm the exact formula with the Jushuitan business rule.

How to Configure on Qeasy

In the Qeasy Data Integration Platform, this API is usually wrapped as the "Jushuitan-Inventory Query" adapter under the Jushuitan Open Platform connector. The recommended configuration steps are:

Connector: Select the Jushuitan adapter and enter partnerId, AppKey, and AppSecret; the platform generates the signature automatically.
Request Template: Bind modified_begin / modified_end to the platform variables {{LAST_SYNC_TIME}} and {{CURRENT_TIME}} to implement the incremental window.
Field Mapper: The Qeasy field mapper automatically flattens datas[*] into rows, using the expression {{sku_id}}-{{wms_co_id}}-{{ts}} as the primary key.
Target: Write into the MySQL table jst_inventory_query; Qeasy generates REPLACE INTO batch writes by default to prevent duplicates.
Post-Script: Mount a script in the AfterTargetGenerate phase to convert empty strings to null and to filter emoji and four-byte characters, avoiding MySQL write errors.
Scheduling: A crontab of 20 */2 * * * (every 2 hours) is recommended; it stays well below the 7-day window and covers most alerting needs.

Cross-Project Practical Points

Never exceed the 7-day window: Many customers have hit this hard limit. Keep a 1–2 hour overlap to avoid missing records.
Use wms_co_id to distinguish warehouses: Both aggregate and per-warehouse queries use the same API but require different parameters; keep the column in MySQL.
Do not be greedy with page_size: We have observed timeouts when page_size=50 and data volume is large; 30 is the safe choice.
Use a composite primary key: i_id alone can collide; sku_id+wms_co_id+ts is the most stable option.
All quantity fields are strings: Store them as VARCHAR or explicitly CAST to DECIMAL; do not treat them as numbers.
Clean empty strings: Jushuitan frequently returns ""; convert them to null before storing to keep MySQL indexes healthy.

Pitfall Recap

Truncated 7-day window: A first-time run with a 30-day window failed immediately. The safe approach is a 2-hour rolling window with a 1-hour overlap on modified.
504 timeouts from large page_size: A retail customer with very large single-warehouse data hit frequent timeouts at page_size=50; reducing to 30 stabilized the integration.
MySQL errors from emoji characters: Product names containing emoji broke utf8 writes; filter four-byte characters in AfterTargetGenerate.
Mixing aggregate and per-warehouse data: Some projects wrote aggregate and per-warehouse data into the same row, doubling the inventory; always split by wms_co_id.
Treating quantities as INT: All quantity fields are strings. Writing "" into an INT column silently becomes 0, skewing sellable stock. Convert to NULL or DECIMAL before writing.

When to Use

Choose this API when you need to localize Jushuitan inventory, perform multi-warehouse aggregation, drive inventory alerts, or feed BI/ERP. If you only need real-time sellable quantity for a single SKU and have no batch-analysis requirement, calling Jushuitan's front-end API directly is lighter. This API is not designed for second-level real-time inventory; the 2-hour cadence is the most cost-effective rhythm.

Authoritative Tutorial on the Jushuitan Inventory Query API Field Manual (MySQL Integration)