Oracle Analytics Cloud and Server

Home› Oracle Analytics› Oracle Analytics Forums› Oracle Analytics Cloud and Server› Oracle Analytics Cloud and Server

Categories

Welcome to the Oracle Analytics Community: Please complete your User Profile and upload your Profile Picture

how to create a dataset with a manual query that includes a json function

Accepted answer
164
Views
9
Comments
User_0K99C
User_0K99C Rank 1 - Community Starter
in Oracle Analytics Cloud and Server

Hello, I ma connected to a mysql heatwave from where I can run these two sql commands without problems.

SELECT v FROM `test`.`SmPolicyAssociation` LIMIT 0, 1000

'{\"associationId\":\"87eed8f3-2784-401b-9960-7484844b0467\",\"createdTimestamp\":1551083246662,\"user\":{\"cid\":43,\"userIds\":{\"IPV6\":\"2001:0001:0022:3286:0000:0000:0000:0000\",\"DNN\":\"dnn1\",\"IPV4\":\"192.168.10.10\",\"SNSSAI\":\"11-abc123\",\"SUPI\":\"imsi-450081000008001\",\"GPSI\":\"9192503899\"}},\"smPolicyDecision\":{\"sessRules\":{\"0_1\":{\"authDefQos\":{\"5qi\":9,\"arp\":{\"priorityLevel\":1,\"preemptCap\":\"MAY_PREEMPT\",\"preemptVuln\":\"NOT_PREEMPTABLE\"}},\"sessRuleId\":\"0_1\"}},\"pccRules\":{\"0_0\":{\"flowInfos\":[{\"flowDescription\":\"permit in ip from any to any\",\"flowDirection\":\"UPLINK\"},{\"flowDescription\":\"permit out ip from any to any\",\"flowDirection\":\"DOWNLINK\"}],\"pccRuleId\":\"0_0\",\"precedence\":3000,\"refQosData\":[\"qosdata_0\"],\"refChgData\":[\"chgdata_0\"]}},\"qosDecs\":{\"qosdata_0\":{\"5qi\":9,\"qosId\":\"qosdata_0\",\"arp\":{\"priorityLevel\":1,\"preemptCap\":\"MAY_PREEMPT\",\"preemptVuln\":\"NOT_PREEMPTABLE\"}}},\"chgDecs\":{\"chgdata_0\":{\"chgId\":\"chgdata_0\"}},\"policyCtrlReqTriggers\":[\"PLMN_CH\",\"UE_IP_CH\",\"DEF_QOS_CH\",\"AC_TY_CH\"]},\"smPolicyContextData\":{\"3gppPsDataOffStatus\":true,\"accNetChId\":{\"accNetChaIdValue\":\"01020304\",\"sessionChScope\":true},\"gpsi\":\"9192503899\",\"supi\":\"imsi-450081000008001\",\"pduSessionId\":1,\"pduSessionType\":\"IPV4\",\"dnn\":\"dnn1\",\"notificationUri\":\http://smf-simulator:8080/smf/notify\,\"accessType\":\"3GPP_ACCESS\",\"ratType\":\"EUTRA\",\"servingNetwork\":{\"mnc\":\"08\",\"mcc\":\"450\"},\"ueTimeZone\":\"+08:00\",\"pei\":\"990000862471854\",\"ipv4Address\":\"192.168.10.10\",\"ipv6AddressPrefix\":\"2001:1:22:3286::/64\",\"online\":false,\"offline\":true,\"sliceInfo\":{\"sst\":11,\"sd\":\"abc123\"},\"userLocationInformation\":{\"nrLocation\":{\"ncgi\":{\"nrCellId\":\"512\",\"plmnId\":{\"mcc\":\"450\",\"mnc\":\"08\"}},\"tai\":{\"plmnId\":{\"mcc\":\"450\",\"mnc\":\"08\"},\"tac\":\"1801\"}}},\"supportedFeatures\":\"\",\"smPoliciesUpdateNotificationUrl\":\"npcf-smpolicycontrol/v1/sm-policies/{ueId}/notify\",\"subSessAmbr\":{\"downlink\":\"1000000 Kbps\",\"uplink\":\"10000 Kbps\"},\"subscribedDefaultQosInformation\":\"FFS\"},\"defaultPccRuleId\":\"0_0\",\"ruleIdCounter\":2,\"qosDataIdCounter\":1,\"chgDataIdCounter\":1,\"precedenceValue\":0,\"modelObjectId\":\"87eed8f3-2784-401b-9960-7484844b0467\"}'

select SUPI, JSON_EXTRACT (v,'$.user.userIds.DNN') as DNN from test.SmPolicyAssociation ;

imsi-450081000008001

"dnn1"

imsi-450081000008002

"dnn1"

imsi-450081000008003

"dnn1"

imsi-450081000008001

"dnn1"

When I try to create a dataset with a manual SQL query the first command works but the second command doesnt work. Any idea why I can not include JSON commands ?

Best Answer

Answers

  • Gianni Ceresa
    Gianni Ceresa Mod

    @User_0K99C ,

    What product are you using? OAS or OAC?

    If you are on OAC you don't really have access to anything, but if you are in OAS you should still be able to see some kind of logs maybe telling you what's going on.

  • User_0K99C
    User_0K99C Rank 1 - Community Starter

    Thanks to both .

    I have changed the approach to it now. I am creating a view of the JSON field to varchar so I dont have problems managing JSON from OAC.

    CREATE
    ALGORITHM = UNDEFINED
    DEFINER = bastion@%
    SQL SECURITY DEFINER
    VIEW roaming AS
    SELECT
    SmPolicyAssociation.SUPI AS SUPI,
    CONVERT( SmPolicyAssociation.k USING UTF8MB3) AS sessionid,
    CONVERT( JSON_UNQUOTE(JSON_EXTRACT(SmPolicyAssociation.v,
    '$.smPolicyContextData.servingNetwork.mcc')) USING UTF8MB3) AS MCC,
    CONVERT( JSON_UNQUOTE(JSON_EXTRACT(SmPolicyAssociation.v,
    '$.user.userIds.DNN')) USING UTF8MB3) AS DNN
    FROM
    SmPolicyAssociation

  • Gianni Ceresa
    Gianni Ceresa Mod
    edited May 20, 2024 5:55PM

    If the code of this view doesn't work directly as source for a dataset in OAC, I would call it a bug.

    Because you weren't returning JSON objects (or the view would also not work when used in OAC), and it's the dataset definition having a bug in not being able to handle something correctly, and also not being able to report the issue with a meaningful error.

    The fact that JSON is not a supported datatype, in my opinion, is not a valid argument for not supporting the JSON functions, mostly because OAC just send the query back to the database and doesn't try to execute the logic itself.

    If you have time, I would still consider valid to raise a SR and report it as a bug.

  • User_0K99C
    User_0K99C Rank 1 - Community Starter

    Ok I will check. Possibly the problem is with the output of the query. Json type fileds are not supported but if I convert them the query should work as you say.

    Thanks

  • Bret Grinslade - Oracle Analytics-Oracle
    Bret Grinslade - Oracle Analytics-Oracle Mod

    The enter manual SQL option in OAC doesn't support all SQL syntax. I would expect it to give you and error and not allow you to save the definition. If you have database access, implementing a view is a good work around and they you will do a pretty simple select in OAC. And Idea Lab suggestion on support either JSON directly or enhancing support for more manual sql is a good idea.

  • Gianni Ceresa
    Gianni Ceresa Mod

    The enter manual SQL option in OAC doesn't support all SQL syntax.

    @Bret Grinslade - Oracle Analytics-Oracle , is there a documented official list of what is supported and what not?

    It's known that the WITH clause isn't supported because the query is generally already embedded inside a WITH clause and some databases (Oracle for example) do not support nested WITH clauses.

    But if there is a long, undefined, list of not supported SQL syntaxes, it kind of kill the reason for the "manual SQL query" to exist: it will always be a lottery to find out of a query is accepted or not. At this point remove it and people at least will not even try and go for a view directly.

  • Michal Zima
    Michal Zima Rank 7 - Analytics Coach

    @Bret Grinslade - Oracle Analytics-Oracle Completely agree with @Gianni Ceresa . Pls, for sake of clarity (and transparency) provide (in standard documenation) list of not supported SQL syntax for manual query definition in data set definition (including this, amongst community already known, but not documented limitation of WITH clause usage in manual query). Thanks.

  • User_0K99C
    User_0K99C Rank 1 - Community Starter

    Thanks Gayathri and Giani,

    I am using OAC.

    After understianding why it was failing what I have done is to create a view of the real table and then converting it to text.

    CREATE
    ALGORITHM = UNDEFINED
    DEFINER = bastion@%
    SQL SECURITY DEFINER
    VIEW roaming AS
    SELECT
    SmPolicyAssociation.SUPI AS SUPI,
    CONVERT( SmPolicyAssociation.k USING UTF8MB3) AS sessionid,
    CONVERT( JSON_UNQUOTE(JSON_EXTRACT(SmPolicyAssociation.v,
    '$.smPolicyContextData.servingNetwork.mcc')) USING UTF8MB3) AS MCC,
    CONVERT( JSON_UNQUOTE(JSON_EXTRACT(SmPolicyAssociation.v,
    '$.user.userIds.DNN')) USING UTF8MB3) AS DNN
    FROM
    SmPolicyAssociation

    OAC can get access to those JSON fields transformed into varchar through the view.