Home » SQL & PL/SQL » SQL & PL/SQL » json fetching using JSON_TABLE (Oracle 12c)
json fetching using JSON_TABLE [message #675773] Thu, 18 April 2019 06:31 Go to next message
sss111ind
Messages: 593
Registered: April 2012
Location: India
Senior Member

--not working
SELECT
    jt.slab_id,
    jt.rule_id,
    substr(jt.group_code, instr(jt.group_code, ':', 1, 1) + 1) group_code,
    jt.no_of_authorizer
FROM
        JSON_TABLE ( '{
	"data": {
		"matrixName": "aaaaaa",		
		"authMode": "Random",		
		"matrixInfo": [{
			"startSlab": "11",
			"endSlab": "222",
			"ruleInfo": [{
				"ruleId": "s1_r1",
				"groupInfo": [{
					"noOfAuth": "1",					
					"groupCode": "DEMOCORP2_fsdg0_"
				}]
			}],
			"slabId": "1"
		},
		{
			"startSlab": "444",
			"endSlab": "55656",
			"ruleInfo": [{
				"ruleId": "s2_r1",
				"groupInfo": [{
					"noOfAuth": "1",					
					"groupCode": "DEMOCORP2_fsdg1_"					
				}]
			},
			{
				"ruleId": "s2_r2",
				"groupInfo": [{
					"noOfAuth": "1",					
					"groupCode": "DEMOCORP2_fsdg2_"					
				}]
			}],
			"slabId": "2"
		}]
	}
}'
        , '$.data'
            COLUMNS (
                slab_id NUMBER PATH '$.matrixInfo[*].slabId',
                rule_id VARCHAR2 ( 30 ) PATH '$.matrixInfo.ruleInfo[*].ruleId',
                NESTED PATH '$.matrixInfo.ruleInfo.groupInfo[*]'
                    COLUMNS (
                        no_of_authorizer NUMBER PATH '$.noOfAuth',
                        priority NUMBER PATH '$.priority',
                        group_code VARCHAR2 ( 150 ) PATH '$.groupCode'
                    )
            )
        )
    AS "JT";


--working

SELECT
    jt.slab_id,
    jt.rule_id,
    substr(jt.group_code, instr(jt.group_code, ':', 1, 1) + 1) group_code,
    jt.no_of_authorizer
FROM
        JSON_TABLE ( '{
	"data": {
		"matrixName": "aaaaaa",		
		"authMode": "Random",	        
		"matrixInfo": [{
            "slabId": "1",
			"startSlab": "11",
			"endSlab": "222",
			"ruleInfo": [{
				"ruleId": "s1_r1",
				"groupInfo": [{
					"noOfAuth": "1",					
					"groupCode": "DEMOCORP2_fsdg0_"
				}]
			}]			
		},
		{   "slabId": "2",
			"startSlab": "444",
			"endSlab": "55656",            
			"ruleInfo": [{
				"ruleId": "s2_r1",
				"groupInfo": [{
					"noOfAuth": "1",					
					"groupCode": "DEMOCORP2_fsdg1_"					
				}]
			},
			{
				"ruleId": "s2_r2",
				"groupInfo": [{
					"noOfAuth": "1",					
					"groupCode": "DEMOCORP2_fsdg2_"					
				}]
			}]			
		}]
	}
}'
        , '$.data'
            COLUMNS (
                slab_id NUMBER PATH '$.matrixInfo[*].slabId',
                rule_id VARCHAR2 ( 30 ) PATH '$.matrixInfo.ruleInfo[*].ruleId',
                NESTED PATH '$.matrixInfo.ruleInfo.groupInfo[*]'
                    COLUMNS (
                        no_of_authorizer NUMBER PATH '$.noOfAuth',
                        priority NUMBER PATH '$.priority',
                        group_code VARCHAR2 ( 150 ) PATH '$.groupCode'
                    )
            )
        )
    AS "JT";	

Hi All,
I could not fetch slabId from the first query however the second query fetched it when i just changed the place of slabIds.
Please help me to find what is going wrong.

Thanks,
Re: json fetching using JSON_TABLE [message #675774 is a reply to message #675773] Thu, 18 April 2019 06:36 Go to previous messageGo to next message
BlackSwan
Messages: 26538
Registered: January 2009
Location: SoCal
Senior Member
See a photo of my car.
my car is not working.
Please help me to find what is going wrong.

We don't have your tables.
We don't have your data.
We don't know your requirements.
We don't know what working correctly looks like.
We can't assist based upon lack of details not provided by you.

Please read and follow the forum guidelines, to enable us to help you:
OraFAQ Forum Guide

Re: json fetching using JSON_TABLE [message #675776 is a reply to message #675774] Thu, 18 April 2019 07:39 Go to previous messageGo to next message
Michel Cadot
Messages: 66437
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Quote:
Please read and follow the forum guidelines
Laughing

Re: json fetching using JSON_TABLE [message #675777 is a reply to message #675776] Thu, 18 April 2019 08:53 Go to previous messageGo to next message
cookiemonster
Messages: 13626
Registered: September 2008
Location: Rainy Manchester
Senior Member
@Blackswan - those are JSON queries, no tables involved, you can copy and paste and run them in your DB as is, assuming your DB is a high enough version.

@sss111ind - expected output and precise oracle version would help.

I get all null values from both in my 12.1.0.2.0
Re: json fetching using JSON_TABLE [message #675792 is a reply to message #675777] Sat, 20 April 2019 03:51 Go to previous messageGo to next message
sss111ind
Messages: 593
Registered: April 2012
Location: India
Senior Member

Sorry for late reply,

We are using the below version.

Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production

Excepted output from first query would be:-


SLAB_ID RULE_ID GROUP_CODE NO_OF_AUTHORIZER
1 s1_r1 DEMOCORP2_fsdg0_ 1
2 s2_r1 DEMOCORP2_fsdg1_ 1
2 s2_r2 DEMOCORP2_fsdg2_ 1

[Updated on: Sat, 20 April 2019 03:52]

Report message to a moderator

Re: json fetching using JSON_TABLE [message #675794 is a reply to message #675792] Sat, 20 April 2019 05:25 Go to previous messageGo to next message
Solomon Yakobson
Messages: 2858
Registered: January 2010
Location: Connecticut, USA
Senior Member
You simply got lucky with "working" one. In general, Oracle should raise error since '$.matrixInfo[*].slabId' doesn't map to a singleton. Same way is '$.ruleInfo[*].ruleId'. Anyway, correct query would be:
SELECT
    jt.slab_id,
    jt.rule_id,
    substr(jt.group_code, instr(jt.group_code, ':', 1, 1) + 1) group_code,
    jt.no_of_authorizer
FROM
        JSON_TABLE ( '{
 "data": {
  "matrixName": "aaaaaa",
  "authMode": "Random",
  "matrixInfo": [{
   "startSlab": "11",
   "endSlab": "222",
   "ruleInfo": [{
    "ruleId": "s1_r1",
    "groupInfo": [{
     "noOfAuth": "1",
     "groupCode": "DEMOCORP2_fsdg0_"
    }]
   }],
   "slabId": "1"
  },
  {
   "startSlab": "444",
   "endSlab": "55656",
   "ruleInfo": [{
    "ruleId": "s2_r1",
    "groupInfo": [{
     "noOfAuth": "1",
     "groupCode": "DEMOCORP2_fsdg1_"
    }]
   },
   {
    "ruleId": "s2_r2",
    "groupInfo": [{
     "noOfAuth": "1",
     "groupCode": "DEMOCORP2_fsdg2_"
    }]
   }],
   "slabId": "2"
  }]
 }
}'
        , '$.data.matrixInfo[*]'
            COLUMNS
              (
               slab_id NUMBER PATH '$.slabId',
               NESTED PATH '$.ruleInfo[*]'
                 COLUMNS
                   (
                    rule_id VARCHAR2 ( 30 ) PATH '$.ruleId',
                    NESTED PATH '$.groupInfo[*]'
                      COLUMNS
                        (
                         no_of_authorizer NUMBER PATH '$.noOfAuth',
                         priority NUMBER PATH '$.priority',
                         group_code VARCHAR2 ( 150 ) PATH '$.groupCode'
                        )
                   )
              )
        )
    AS "JT"
/

   SLAB_ID RULE_ID                        GROUP_CODE           NO_OF_AUTHORIZER
---------- ------------------------------ -------------------- ----------------
         1 s1_r1                          DEMOCORP2_fsdg0_                    1
         2 s2_r1                          DEMOCORP2_fsdg1_                    1
         2 s2_r2                          DEMOCORP2_fsdg2_                    1

SQL> 

SY.

[Updated on: Sat, 20 April 2019 05:40]

Report message to a moderator

Re: json fetching using JSON_TABLE [message #675795 is a reply to message #675794] Sat, 20 April 2019 08:24 Go to previous message
sss111ind
Messages: 593
Registered: April 2012
Location: India
Senior Member

Thank you All,

Thank you SY for pointing out the mistake, its working.

[Updated on: Sat, 20 April 2019 08:24]

Report message to a moderator

Previous Topic: Comparing daywise data
Next Topic: update collection of object type using update statement
Goto Forum:
  


Current Time: Sat Jun 15 19:56:18 CDT 2019