时间戳

首页 > 解决方案 > AS400 中的 Json_table

ibm-midrange - AS400 中的 Json_table

问题描述

我正在使用下面的带有 json 的 sql 查询，但我无法检索数据，检索到 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;

标签： ibm-midrange

解决方案

添加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;

推荐阅读