parseJsonTable

Field paths start at the outermost JSON object. The key names below are placeholders for actual keys; index is a zero-based array index.

For key names containing special characters such as dots, spaces, or brackets, enclose the complete name in double quotes within brackets.

When path is specified:

• The outermost JSON value must be an object, not an array such as [{"id":1},{"id":2}]. Objects can contain arrays.

• If json is a STRING vector, each element must contain exactly one JSON object.

• A missing path (including an array index out of range), JSON null, or a type conversion failure produces a NULL of the corresponding type. Arrays built from multiple paths retain the positions of missing values.

• Invalid path syntax or incomplete JSON causes an error.

json1='{"ID":1, "NAME":"cc"}{"NAME":"dd"}'
parseJsonTable(json1)

json2 = '{"col_test":"20190522150407"}'
schemaTB = table(["col_test"] as name, ["DATETIME"] as type, ["yyyyMMddHHmmss"] as format)
parseJsonTable(json2, schemaTB)

json3='{"ID":11, "NAME":"dd"}'
schemaTB1 = table(["ID", "NAME", "col_test"] as name, ["INT", "STRING", "DATETIME"] as type, [,,"yyyyMMddHHmmss"] as format)
parseJsonTable(concat([json2,json3]),schemaTB1)

parseJsonTable([json2,json3],schemaTB1)

Example 5: Parse a string vector containing an empty JSON object, which is retained as one row.

home = ['{"Num":10, "Name":"Ronaldo","Goal":"3","MatchDay":"20120322"}','{"Num":3, "Name":"Carlos","Goal":"1","MatchDay":"20120322"}','{}'];
schemaLiga = table(["Num","Name","Goal","MatchDay"] as name, ["INT","STRING","INT","DATE"] as type, [,,,"yyyyMMdd"] as format);
formation = parseJsonTable(home,schemaLiga);
formation;

Example 6: Use path to combine nested trade fields with the receipt time from the root object.

quoteJson = [
    '{"data":{"id":1,"price":10.1},"receivedTime":"2026.01.01 09:00:00.000"}',
    '{"data":{"id":2,"price":10.2},"receivedTime":"2026.01.01 09:00:01.000"}'
]
quoteSchema = table(
    ["id", "price", "receivedTime"] as name,
    ["LONG", "DOUBLE", "TIMESTAMP"] as type,
    ["data.id", "data.price", "receivedTime"] as path
)
parseJsonTable(json=quoteJson, schema=quoteSchema)

Example 7: Read root fields and position fields from columnsAfter, ignore columnsBefore, and use format to specify the date string format.

positionJson = '{"OWNER":"TRADE","operType":"U","columnsAfter":{"L_DATE":"20250604","L_UNIT_ID":690792,"L_CURRENT_AMOUNT":5000},"columnsBefore":{"L_DATE":"20250603","L_UNIT_ID":690792,"L_CURRENT_AMOUNT":4500}}'
positionSchema = table(
    ["owner", "operation", "positionDate", "unitId", "amount"] as name,
    ["STRING", "STRING", "DATE", "LONG", "LONG"] as type,
    ["", "", "yyyyMMdd", "", ""] as format,
    ["OWNER", "operType", "columnsAfter.L_DATE", "columnsAfter.L_UNIT_ID", "columnsAfter.L_CURRENT_AMOUNT"] as path
)
parseJsonTable(json=positionJson, schema=positionSchema)

Example 8: Build array vectors for five bid and ask prices in level order, retaining NULL for the missing second bid price.

depthJson = '{"stock_code":"IF2403","buy_order_book":{"buy_first_price":3598.8,"buy_third_price":3598.4,"buy_fourth_price":3598.2,"buy_fifth_price":3598.0},"sell_order_book":{"sell_first_price":3599.0,"sell_second_price":3599.2,"sell_third_price":3599.4,"sell_fourth_price":3599.6,"sell_fifth_price":3599.8}}'
buyPricePaths = [
    "buy_order_book.buy_first_price",
    "buy_order_book.buy_second_price",
    "buy_order_book.buy_third_price",
    "buy_order_book.buy_fourth_price",
    "buy_order_book.buy_fifth_price"
]
sellPricePaths = [
    "sell_order_book.sell_first_price",
    "sell_order_book.sell_second_price",
    "sell_order_book.sell_third_price",
    "sell_order_book.sell_fourth_price",
    "sell_order_book.sell_fifth_price"
]
depthPaths = ("stock_code", buyPricePaths, sellPricePaths)
depthSchema = table(
    ["stockCode", "buyPrice", "sellPrice"] as name,
    ["STRING", "DOUBLE[]", "DOUBLE[]"] as type,
    depthPaths as path
)
parseJsonTable(json=depthJson, schema=depthSchema)

Example 9: Read array elements and nested keys containing dots.

orderJson = '{"orderId":"A001","items":[{"price":10.5},{"price":12.0}],"data.id":{"child.value":3}}'
orderSchema = table(
    ["orderId", "firstPrice", "nestedValue"] as name,
    ["STRING", "DOUBLE", "INT"] as type,
    ["orderId", "items[0].price", '["data.id"]["child.value"]'] as path
)
parseJsonTable(json=orderJson, schema=orderSchema)

Example 10: Handle a missing field, JSON null, a type conversion failure, and an array index out of range. Blank table cells indicate NULL.

statusJson = '{"orderId":"A001","data":{"price":null,"quantity":"unknown"},"items":[{"price":10.5}]}'
statusSchema = table(
    ["orderId", "missingPrice", "nullPrice", "invalidQuantity", "outOfRangePrice"] as name,
    ["STRING", "DOUBLE", "DOUBLE", "DOUBLE", "DOUBLE"] as type,
    ["orderId", "data.missingPrice", "data.price", "data.quantity", "items[1].price"] as path
)
parseJsonTable(json=statusJson, schema=statusSchema)