How to "select where" within json in Oracle SQL Developer?

user_9822083 0
Viewed 417

I have a table called DISCOUNT_CODES with two columns, one having JSON values:

CODE           ACTIVE
1234           {"active":"Y"}
2468           {"active":"N"}
1359           {"active":"Y"}

As the second column is in JSON, I was wondering if there was a way I can select the codes that are active without having to do a select query like:

SELECT CODE FROM DISCOUNT_CODES WHERE ACTIVE = '{"active":"Y"}';

In pseudo-code, I am wondering if something like this is possible:

SELECT CODE FROM DISCOUNT_CODES WHERE JSON(ACTIVE $.active) = "Y";

Edit: I am using Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production

oracle12c select oracle sql json
2 Answers

Yes, you can use a SELECT statement containing JSON_TABLE() function for the DB version 12c+ such as the one below :

SELECT d.code
  FROM discount_codes d
 CROSS JOIN JSON_TABLE(active, '$' COLUMNS (
                                            active VARCHAR2(100) PATH '$.active'
                                           )
                      ) j
 WHERE j.active = 'Y'

Demo

user_58413060

You can also use JSON_EXISTS

SELECT CODE FROM DISCOUNT_CODES
WHERE json_exists(ACTIVE, '$?(@.active == "Y")');
user_11189780
Related