JSON format using JSON_OBJECT

Question

I want to achieve the below JSON format using JSON_OBJECT. Please help.

{
  "abc": {
    "input": 10,
    "max": 20,
    "check": 30
  },
  "xyz": {
    "income": 198000
  }
}

Try to achieve the same using the below query.

SELECT JSON_OBJECT
(
                    'agri_Expense' VALUE 
       JSON_OBJECT(
                    'input'     VALUE 10,
                    'max'       VALUE 20,
                    'check'     VALUE 30
                  )  JSON_OBJECT('xyz'
                    JSON_OBJECT('income' VALUE( 19800 ) )
        
)
 FROM DUAL

Please help. syntax issue.

Popeye · Accepted Answer · 2020-07-02 07:54:57Z

1

You need something like this:

SQL> SELECT JSON_OBJECT (
  2  'agri_Expense' VALUE
  3      JSON_OBJECT ('abc' value
  4                      JSON_OBJECT( 'input' VALUE 10, 'max' VALUE 20, 'check' VALUE 30),
  5                   'xyz' VALUE
  6                      JSON_OBJECT ( 'income' VALUE 19800 ))
  7  )
  8    FROM DUAL;

JSON_OBJECT('AGRI_EXPENSE'VALUEJSON_OBJECT('ABC'VALUEJSON_OBJECT('INPUT'VALUE10,
--------------------------------------------------------------------------------
{"agri_Expense":{"abc":{"input":10,"max":20,"check":30},"xyz":{"income":19800}}}

SQL>

Update:

SQL> SELECT
  2  JSON_OBJECT ('abc' value
  3                  JSON_OBJECT( 'input' VALUE 10, 'max' VALUE 20, 'check' VALUE 30),
  4               'xyz' VALUE
  5                   JSON_OBJECT ( 'income' VALUE 19800 ))
  6    FROM DUAL;

JSON_OBJECT('ABC'VALUEJSON_OBJECT('INPUT'VALUE10,'MAX'VALUE20,'CHECK'VALUE30),'X
--------------------------------------------------------------------------------
{"abc":{"input":10,"max":20,"check":30},"xyz":{"income":19800}}

SQL>

edited Jul 2, 2020 at 7:54

answered Jul 2, 2020 at 7:45

Popeye

36k4 gold badges12 silver badges31 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

NIKHIL KULSHRESTHA Over a year ago

don't look my query just see the JSON structure. Query is just for your reference how I'm trying as in my query i no need agri_Expense. I need exact JSON structure which I shared

NIKHIL KULSHRESTHA Over a year ago

OK, That is working. Can you please help me in the below structure as well. "abcz": [ { "Field_input3": 45000, : E51 "Minimum": "Yes", "Check_Input": 108000, : F51 "Tractor_Cost": "Driver Salary" : } ]

Popeye Over a year ago

This should be another question and yes, this is not proper JSON

Collectives™ on Stack Overflow

JSON format using JSON_OBJECT

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related