CODEWITHSUNDEEP
sqljsondatabaseoracle-database
Munjal
Munjal

Reputation: 37

Fetch JSON from CLOB in SQL Developer

I have a clob field in table with json data in it. And I want to get one value from that json data and process on it. how to get it?

Example: Table A has 2 columns: id and accounts, where id is string and account is a clob with json data.

Json data is stored like below:

{
    "facebook":"[email protected]",
    "gmail":"[email protected]",
    "instagram":"[email protected]"
}

Now I want to get value of gmail form the clob in the table and it should give it like:

ID Gmail

A1 [email protected]

C2 [email protected]

...

Thanks in advance for your help!

Upvotes: 0

Views: 187

Answers (1)

EJ Egyed
EJ Egyed

Reputation: 6064

If you are on Oracle Database 12.1.0.2 or higher, you can use JSON_VALUE

WITH
    table_a (id, accounts)
    AS
        (SELECT 'A1', EMPTY_CLOB () || '{
    "facebook":"[email protected]",
    "gmail":"[email protected]",
    "instagram":"[email protected]"
}' FROM DUAL
         UNION ALL
         SELECT 'C2', EMPTY_CLOB () || '{
    "facebook":"[email protected]",
    "gmail":"[email protected]",
    "instagram":"[email protected]"
}' FROM DUAL
         UNION ALL
         SELECT 'B3', EMPTY_CLOB () || '{
    "facebook":"[email protected]"
}' FROM DUAL)
SELECT id, json_value (accounts, '$.gmail') AS gmail
  FROM table_a;


   ID          GMAIL
_____ ______________
A1    [email protected]
C2    [email protected]
B3

Upvotes: 2

Related Questions