nav[aria-label="Primary Navigation"] { padding: 0; & ul { list-style: none; width: 100%; display: flex; flex-direction: row; justify-content: start; align-items: start; gap: 30px; padding: 0; & li { margin: 0; } & ul li { list-style: none; } } }

Oracle Communities

Oracle Analytics and AI

Child Item

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

User_0K99C

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 ?

Find more posts tagged with

Accepted answers

GayathriAnand-Oracle

Based on supported data types documentation, json is not a supported data type. It might be a limitation that we don't support json functions either. Seems like a candidate for idea lab request. Other comments. welcomed.

Thanks

Gayathri

All comments

GayathriAnand-Oracle

Thanks

Gayathri

Gianni Ceresa

@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

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

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

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

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

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

@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

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.

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