sqljsonteradatateradata-sql-assistant

JSON and Teradata

I have the following JSON:

'{"0": false,"1": false,"barring": "BAR_ROAMING"}'

There is a propriety in teradata for Json that can be used to extract barring value F_JSON.barring --> BAR_ROAMING

But for the other 2, which are dynamic keys, how can I extract them?

Solution

  • I ended up using

    CREATE TABLE KEY_JSON AS (
SELECT DISTINCT(JSONKeys) J_KEY FROM Json_Keys
    (
    ON (SELECT JSON FROM JSON_TABLE) USING QUOTES('N'))
    AS json_data) WITH DATA;

    And performing a JOIN between my 2 tables (JSON_TABLE and KEY_JSON) ON JSON LIKE '%||J_KEY||%'

    And extracting the value using JSONEXTRACT(JSON.'$."||J_KEY)