Создать вложенный JSON oracle19c с помощью функций JSON

Привет, у меня есть пример запроса ниже. В реальном случае в каждом представлении гораздо больше столбцов. Представление A имеет уникальные идентификаторы и присутствует в других представлениях. Я пытался объединить все представления и сгенерировать JSON, используя JSON_OBJECT и JSON_ARRAYAGG, но соединение приводит к появлению дубликатов в моем окончательном JSON. Я попытался создать отдельный массив JSON и подумывал об объединении, но не смог заставить его работать. Ниже приведен образец CTE для данных. По сути, три представления — это JSON_OBJECT внутри ARRAY. Если в представлениях B, C, D нет данных для соответствующего идентификатора в A, тогда не будет объекта JSON для этого представления. Пример 301 отсутствует в представлении D.

WITH A AS(SELECT 101 AS ID ,DATE '2024-01-03' AS dt FROM dual UNION
          SELECT 201 AS ID ,DATE '2024-03-13' AS dt FROM dual UNION 
          SELECT 301 AS ID ,DATE '2024-05-23' AS dt FROM dual ),
          
    B AS (SELECT 101 AS ID, 'ABC' AS typ, 10 AS price FROM dual UNION
          SELECT 101 AS ID, 'XYZ' AS typ, 20 AS price FROM dual UNION
          SELECT 101 AS ID, 'LMY' AS typ, 40 AS price FROM dual UNION
          SELECT 201 AS ID, 'PQR' AS typ, 30 AS price FROM dual UNION
          SELECT 301 AS ID, 'MNP' AS typ, 10 AS price FROM dual ),
          
    C AS (SELECT 101 AS ID, 'NY' AS place FROM dual UNION
          SELECT 101 AS ID, 'NJ' AS Place FROM dual UNION
          SELECT 201 AS ID, 'PA' AS Place FROM dual UNION
          SELECT 301 AS ID, 'VT' AS Place FROM dual UNION
          SELECT 301 AS ID, 'MT' AS Place FROM dual),
          
    D AS (SELECT 101 AS ID, 'BLACK' AS color FROM dual UNION
          SELECT 101 AS ID, 'WHITE' AS color FROM dual UNION
          SELECT 201 AS ID, 'PINK' AS color FROM dual UNION
          SELECT 201 AS ID, 'GREEN' AS color FROM dual

ожидаемый результат:

[
    {
        "id": "101",
        "dt": "2024-01-03 12:00:00",
        "types": [
            {
                "typ": "ABC",
                "price": 10
            },
            {
                "typ": "XYZ",
                "price": 20
            },
            {
                "typ": "LMY",
                "price": 40
            }
        ],
        "events": [
            {
                "place": "NY"
            },
            {
                "place": "NJ"
            }
        ],
        "pattern": [
            {
                "color": "BLACK"
            },
            {
                "color": "WHITE"
            }
        ]
    },
    {
        "id": "201",
        "dt": "2024-03-13 12:00:00",
        "types": [
            {
                "typ": "PQR",
                "price": 30
            }
        ],
        "events": [
            {
                "place": "PA"
            }
        ],
        "pattern": [
            {
                "color": "PINK"
            },
            {
                "color": "GREEN"
            }
        ]
    },
    {
        "id": "301",
        "dt": "2024-05-23 12:00:00",
        "types": [
            {
                "typ": "MNP",
                "price": 10
            }
        ],
        "events": [
            {
                "place": "VT"
            },
            {
                "place": "MT"
            }
        ]
    }
]

sql oracle plsql oracle12c oracle19c

08.06.2024 04:00

ReactJs | Supabase | Добавление данных в базу данных

Это и есть ваш редактор таблиц в supabase.👇

Понимание Python и переход к SQL

Перед нами лабораторная работа по BloodOath:

Перейти к ответу Данный вопрос помечен как решенный

Ответы 1

Ответ принят как подходящий

Попробуй это:

select 
    json_arrayagg(
        json_object( 
            key 'id' is to_char(a.id),
            key 'dt' is a.dt+0.5,
            key 'types' is
            (select json_arrayagg(
                    json_object( 
                        key 'typ' is b.typ,
                        key 'price' is b.price
                    )
                )
                from b where b.id = a.id
            ) ,
            key 'events' is
            (select json_arrayagg(
                    json_object( 
                        key 'place' is c.place
                    )
                )
                from c where c.id = a.id
            ),
            key 'pattern' is
            (select json_arrayagg(
                    json_object( 
                        key 'color' is d.color
                    )
                )
                from d where d.id = a.id
            ) absent on null
        )
    ) as js
from a
;



 [
  {
    "id": "101",
    "dt": "2024-01-03T12:00:00",
    "types": [
      {
        "typ": "ABC",
        "price": 10
      },
      {
        "typ": "LMY",
        "price": 40
      },
      {
        "typ": "XYZ",
        "price": 20
      }
    ],
    "events": [
      {
        "place": "NJ"
      },
      {
        "place": "NY"
      }
    ],
    "pattern": [
      {
        "color": "BLACK"
      },
      {
        "color": "WHITE"
      }
    ]
  },
  {
    "id": "201",
    "dt": "2024-03-13T12:00:00",
    "types": [
      {
        "typ": "PQR",
        "price": 30
      }
    ],
    "events": [
      {
        "place": "PA"
      }
    ],
    "pattern": [
      {
        "color": "GREEN"
      },
      {
        "color": "PINK"
      }
    ]
  },
  {
    "id": "301",
    "dt": "2024-05-23T12:00:00",
    "types": [
      {
        "typ": "MNP",
        "price": 10
      }
    ],
    "events": [
      {
        "place": "MT"
      },
      {
        "place": "VT"
      }
    ]
  }
]

Огромное спасибо, все получилось отлично! Извините за задержку с ответом.

— 25.06.2024 15:52

08.06.2024 13:15