AS400中的Json_table

Question

我正在使用下面的sql查询和json，但我无法检索数据，检索到0行，100条记录未找到错误。有什么建议吗

select *                                                                  
  from json_table('"fetchData":[{
    "vehicleIdOutput":8005451,
    "sequenceNumberOutput":0}]',
    '$' 
     COLUMNS ( nested '$.fetchdata[*]'                                                         
     columns (  vehicleIdOutput  decimal(7, 0) PATH '$..vehicleIdOutput',           
       sequenceNumberOutput decimal(7, 0) PATH '$..sequenceNumberOutput'  
      ) )) as t; 

Answer 1

添加error on error以查看DB2不会将您的输入视为有效的JSON

select *                                                                  
  from json_table('"fetchData":[{
    "vehicleIdOutput":8005451,
    "sequenceNumberOutput":0}]',
    '$' 
     COLUMNS ( nested '$.fetchdata[*]'                                                         
     columns (  vehicleIdOutput  decimal(7, 0) PATH '$..vehicleIdOutput',           
       sequenceNumberOutput decimal(7, 0) PATH '$..sequenceNumberOutput'  
      ) )
      error on error) as t;

您可以使用大括号告诉最外层的对象是一个对象

select *                                                                  
  from json_table('{"fetchData":[{
    "vehicleIdOutput":8005451,
    "sequenceNumberOutput":0}]}',
    '$.fetchData'
     COLUMNS (
        vehicleIdOutput decimal(7, 0) path '$.vehicleIdOutput',
        sequenceNumberOutput decimal(7, 0) path '$.sequenceNumberOutput'
        )
      error on error ) as t; 

或者删除fetchData并直接使用数组

select *                                                                  
  from json_table('[{
    "vehicleIdOutput":8005451,
    "sequenceNumberOutput":0}]',
    '$'
     COLUMNS (
        vehicleIdOutput decimal(7, 0) path '$.vehicleIdOutput',
        sequenceNumberOutput decimal(7, 0) path '$.sequenceNumberOutput'
        )
      error on error ) as t;