json - Postgresql 错误：用作表达式的子查询返回多行

Question

我正在写这段代码：

 select row_to_json(t)

     from (
 select json_agg(A) from (

        select DISCIPLINE.D_ID,DISCIPLINE.D_NAME AS Discipline_Name,

    (
            select json_agg(B) from 
(

            select CATEGORY.C_ID,CATEGORY.C_NAME,

                (
                    select json_agg(C) from 
                    (
                        select SUB_CATEGORY.SC_ID,SUB_CATEGORY.SC_NAME
                        from SUB_CATEGORY 
                        WHERE SUB_CATEGORY .C_ID=CATEGORY.C_ID
                    )C
                ) 
                from CATEGORY
                WHERE CATEGORY.D_ID=DISCIPLINE.D_ID
            )B
        )
        from DISCIPLINE 
    )A
)t  

我收到此错误：

错误：用作表达式的子查询返回多行

如何解决这个问题？

标签： jsonpostgresql

Answer 1

可能是您的内部子查询返回多于一行，因此如果需要，您应该添加限制 1

    select SUB_CATEGORY.SC_ID,SUB_CATEGORY.SC_NAME 
    from SUB_CATEGORY 
    WHERE SUB_CATEGORY .C_ID=CATEGORY.C_ID
    LIMIT 1

所以

    select row_to_json(t)
     from (
      select json_agg(A) from (
       select DISCIPLINE.D_ID,DISCIPLINE.D_NAME AS Discipline_Name, (
        select json_agg(B) from (
         select CATEGORY.C_ID,CATEGORY.C_NAME, (
          select json_agg(C) from (
                select SUB_CATEGORY.SC_ID,SUB_CATEGORY.SC_NAME 
                from SUB_CATEGORY 
                WHERE SUB_CATEGORY .C_ID=CATEGORY.C_ID
                LIMIT 1
          ) C
         ) from CATEGORY WHERE CATEGORY.D_ID=DISCIPLINE.D_ID
        ) B
       ) from DISCIPLINE 
     ) A
    ) t