I've employee_roles table it stores many organization id in one row by array

gankhulug ganbold 0
Viewed 29

the column names along with the sample values from the employee_roles table:

user_id : "1"
org_id : ["1", "2"]

I want to get org_id and org_name by one row with user_id please help me?

oracle sql json
1 Answers

You can use JSON_TABLE() function, if the currently used DB version is at least 12.1.0.2, in order to return the values row-wise such as

 SELECT user_id, j.org_id
   FROM employee_roles , 
        JSON_TABLE(org_id, '$'
                        COLUMNS (NESTED PATH '$[*]'
                                 COLUMNS (
                                          org_id VARCHAR2 PATH '$'
                                         ) 
                                 ) 
                  ) j

Demo

Barbaros Özhan0
Related