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

Question

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

what version db do you have? the native JSON() support in Oracle starts with 12c, before that you might need a helper package (APEX has one for example) — thatjeffsmith
– thatjeffsmith, Commented Aug 6, 2020 at 20:03
I am using Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production — Jett
– Jett, Commented Aug 6, 2020 at 20:10

Barbaros Özhan · Accepted Answer · 2020-08-06 19:51:03Z

1

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

answered Aug 6, 2020 at 19:51

Barbaros Özhan

65.9k11 gold badges36 silver badges64 bronze badges

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

Comments

Barbaros Özhan · Accepted Answer · 2020-08-06 19:59:00Z

1

You can also use JSON_EXISTS

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

edited Aug 6, 2020 at 19:59

Barbaros Özhan

65.9k11 gold badges36 silver badges64 bronze badges

answered Aug 6, 2020 at 19:53

Derviş Kayımbaşıoğlu

30.9k4 gold badges55 silver badges79 bronze badges

Collectives™ on Stack Overflow

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

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related