How do I write the correct request to output information to "get"? SQL-CodePudding

I immediately apologize for my English to you. The fact is that I need to write a get request for my application with recipes. I want to make the request look like this:

{
  "items": [
    {
      "id_recipe": 1,
      "ingredients": [
        {
          "name_ingredient": "Ingredient1",
          "counts": 30,
          "name_unit": "unit1"
        },
        {
          "name_ingredient": "Ingredient2 ",
          "counts": 1,
          "name_unit": "unit2 "
        },
        {
          "name_ingredient": "Ingredient3",
          "counts": 2,
          "name_unit": "unit1 "
        }
      ]
    },
    {
      "id_recipe": 2,
      "ingredients": [
        {
          "name_ingredient": "Ingredient2",
          "counts": 1,
          "name_unit": "unit3 "
        },
        {
          "name_ingredient": "Ingredient1",
          "counts": 400,
          "name_unit": "unit4"
        }
      ]
    }
  ]
}

But it looks like this

{
  "items": [
    {
      "id_recipe": 1,
      "name_ingredient": "Ingredient1",
      "counts": 30,
      "name_unit": "unit1"
    },
    {
      "id_recipe": 1,
      "name_ingredient": "Ingredient2 ",
      "counts": 1,
      "name_unit": "unit2 "
    },
    {
      "id_recipe": 1,
      "name_ingredient": "Ingredient3",
      "counts": 2,
      "name_unit": "unit1 "
    },
    {
      "id_recipe": 2,
      "name_ingredient": "Ingredient2",
      "counts": 1,
      "name_unit": "unit3 "
    },
    {
      "id_recipe": 2,
      "name_ingredient": "Ingredient1",
      "counts": 400,
      "name_unit": "unit4"
    }
  ]
}

That is, it is necessary to somehow combine elements with the same id_recept into an array. But I do not know how! Here's the code I'm using now:

SELECT PRODUCTS.ID_RECIPE, INGREDIENTS.NAME_INGREDIENT, PRODUCTS.COUNTS, UNITS_OF_MEASUREMENT.NAME_UNIT 
FROM PRODUCTS, INGREDIENTS, UNITS_OF_MEASUREMENT 
WHERE PRODUCTS.ID_INGREDIENT = INGREDIENTS.ID_INGREDIENT 
AND PRODUCTS.ID_MEASUREMENT = UNITS_OF_MEASUREMENT.ID_MEASUREMENT 
ORDER BY ID_RECIPE

This is how the table data looks like: table ingredients

table products

I use oracle. I will be glad if you can help!

CodePudding user response：

You can try something like this (as I don't have neither your ddl nor your data):

select json_object('table name' value table_name,'columns' value json_arrayagg(json_object('column name' value column_name, 'type' value data_type) returning clob pretty) returning clob pretty)
from dba_tab_columns
where table_name like 'DBA_HIST_SQL%'
group by table_name;

output is like this:

{
  "table name" : "DBA_HIST_SQLBIND",
  "columns" : [
  {"column name":"SNAP_ID","type":"NUMBER"},
  {"column name":"CON_ID","type":"NUMBER"},
  {"column name":"CON_DBID","type":"NUMBER"},
  {"column name":"VALUE_ANYDATA","type":"ANYDATA"},
  {"column name":"VALUE_STRING","type":"VARCHAR2"},
  {"column name":"LAST_CAPTURED","type":"DATE"},
  {"column name":"WAS_CAPTURED","type":"VARCHAR2"},
  {"column name":"MAX_LENGTH","type":"NUMBER"},
  {"column name":"SCALE","type":"NUMBER"},
  {"column name":"PRECISION","type":"NUMBER"},
  {"column name":"CHARACTER_SID","type":"NUMBER"},
  {"column name":"DATATYPE_STRING","type":"VARCHAR2"},
  {"column name":"DATATYPE","type":"NUMBER"},
  {"column name":"DUP_POSITION","type":"NUMBER"},
  {"column name":"POSITION","type":"NUMBER"},
  {"column name":"NAME","type":"VARCHAR2"},
  {"column name":"SQL_ID","type":"VARCHAR2"},
  {"column name":"INSTANCE_NUMBER","type":"NUMBER"},
  {"column name":"DBID","type":"NUMBER"}
]
}"
"{
  "table name" : "DBA_HIST_SQLCOMMAND_NAME",
  "columns" : [
  {"column name":"DBID","type":"NUMBER"},
  {"column name":"CON_ID","type":"NUMBER"},
  {"column name":"CON_DBID","type":"NUMBER"},
  {"column name":"COMMAND_NAME","type":"VARCHAR2"},
  {"column name":"COMMAND_TYPE","type":"NUMBER"}
]
}