Passing XML

Answered

Follow

1

Jonas Seemann

• October 23, 2018 13:09

Hi,

we face the problem that sometimes, while parsing a JSON-File, there are extra objects, and sometimes there are not.

 {
            "account_id": "ACTID",
            "campaign_id": "CID",
            "adset_id": "ADSETID",
            "account_name": "CLIENT",
            "campaign_name": "Campaign",
            "adset_name": "Adset",
            "ad_name": "Ad",
            "impressions": "99",
            "date_start": "2018-05-17",
            "date_stop": "2018-05-17",
            "spend": "0",
            "frequency": "1.32",
            "reach": "75",
            "clicks": "0",
            "total_action_value": "0",
            "ad_id": "23842799890540007",
            "unique_clicks": "0"
        },
        {
            "account_id": "ACTID",
            "campaign_id": "CID",
            "adset_id": "ADSETID",
            "account_name": "Client",
            "actions": [
                {
                    "action_type": "link_click",
                    "value": "1"
                },
                {
                    "action_type": "page_engagement",
                    "value": "1"
                },
                {
                    "action_type": "post_engagement",
                    "value": "1"
                }
            ],
            "campaign_name": "Campaign",
            "adset_name": "AdSet",
            "ad_name": "Ad",
            "impressions": "149",
            "date_start": "2018-05-18",
            "date_stop": "2018-05-18",
            "spend": "0.66",
            "frequency": "1.231405",
            "reach": "121",
            "clicks": "1",
            "total_action_value": "0",
            "ad_id": "23842799890540007",
            "unique_clicks": "1"
        }

As you can see, for 2018-05-17 there are no "action_type" - values.

If we use this code:

SELECT 
"xmlTable.idColumn",
"xmlTable.account_id",
"xmlTable.campaign_id",
"xmlTable.adset_id",
"xmlTable.account_name",
"xmlTable.campaign_name",
"xmlTable.adset_name",
"xmlTable.ad_name",
"xmlTable.impressions",
"xmlTable.date_start",
"xmlTable.date_stop",
"xmlTable.spend",
"xmlTable.frequency",
"xmlTable.reach",
"xmlTable.clicks",
"xmlTable.total_action_value",
"xmlTable.ad_id",
"xmlTable.unique_clicks",
a.action_type,
a.action_value
FROM 
(exec "Facebook".invokeHTTP(endpoint=>'https://graph.facebook.com/v3.0/750079528668291/insights',action=>'GET',requestContentType=>'application/json')) w,
XMLTABLE('/root/data' PASSING JSONTOXML('root',to_chars(w.result,'UTF-8'))
    COLUMNS 
    "idColumn" FOR ORDINALITY,
    "account_id" STRING  PATH 'account_id',
    "campaign_id" STRING  PATH 'campaign_id',
    "adset_id" STRING  PATH 'adset_id',
    "account_name" STRING  PATH 'account_name',
    "campaign_name" STRING  PATH 'campaign_name',
    "adset_name" STRING  PATH 'adset_name',
    "ad_name" STRING  PATH 'ad_name',
    "impressions" STRING  PATH 'impressions',
    "date_start" STRING  PATH 'date_start',
    "date_stop" STRING  PATH 'date_stop',
    "spend" STRING  PATH 'spend',
    "frequency" STRING  PATH 'frequency',
    "reach" STRING  PATH 'reach',
    "clicks" STRING  PATH 'clicks',
    "total_action_value" STRING  PATH 'total_action_value',
    "ad_id" STRING  PATH 'ad_id',
    "unique_clicks" STRING  PATH 'unique_clicks',
    "actions" XML PATH '.'
) "xmlTable",
XMLTABLE('actions' PASSING "actions"
    COLUMNS
    "action_type" STRING PATH 'action_type',
    "action_value" STRING PATH 'value'
)a
;;

we won't get the data for 2018-05-17. The reason is probably because "actions" doesn't exist.

As we don't want to use a IF-function, which checks if either "actions" is present or not, we prefer a solution like

 "xmlTable",EXISTS(
XMLTABLE('actions' PASSING "actions"
    COLUMNS
    "action_type" STRING PATH 'action_type',
    "action_value" STRING PATH 'value'
))a
;;

Or something similar.

Do you have any suggestions or ideas? Any help is appreciated.

Thanks and best regards,

Jonas

• Comment actions Permalink

  Official comment

  

  Niklas Schmidtmer

  • October 23, 2018 13:57

  Hi Jonas,

   

  thank you for using the community! Instead of parsing the actions inside the main XMLTABLE statement, you can move it to a separate XMLTABLE statement and do a LEFT JOIN. For the LEFT JOIN you will need to also retrieve the columns that compose the primary key (ad_id and date_start I guess):

  SELECT 
  "xmlTable.account_id",
  "xmlTable.campaign_id",
  "xmlTable.adset_id",
  "xmlTable.account_name",
  "xmlTable.campaign_name",
  "xmlTable.adset_name",
  "xmlTable.ad_name",
  "xmlTable.impressions",
  "xmlTable.date_start",
  "xmlTable.date_stop",
  "xmlTable.spend",
  "xmlTable.frequency",
  "xmlTable.reach",
  "xmlTable.clicks",
  "xmlTable.total_action_value",
  "xmlTable.ad_id",
  "xmlTable.unique_clicks",
  a.action_type,
  a.action_value
  FROM (exec "Facebook".invokeHTTP(endpoint=>'https://graph.facebook.com/v3.0/750079528668291/insights',action=>'GET',requestContentType=>'application/json')) w
  CROSS JOIN
   XMLTABLE('/root/data' PASSING JSONTOXML('root',to_chars(w.result,'UTF-8'))
   COLUMNS 
   "account_id" STRING PATH 'account_id',
   "campaign_id" STRING PATH 'campaign_id',
   "adset_id" STRING PATH 'adset_id',
   "account_name" STRING PATH 'account_name',
   "campaign_name" STRING PATH 'campaign_name',
   "adset_name" STRING PATH 'adset_name',
   "ad_name" STRING PATH 'ad_name',
   "impressions" STRING PATH 'impressions',
   "date_start" STRING PATH 'date_start',
   "date_stop" STRING PATH 'date_stop',
   "spend" STRING PATH 'spend',
   "frequency" STRING PATH 'frequency',
   "reach" STRING PATH 'reach',
   "clicks" STRING PATH 'clicks',
   "total_action_value" STRING PATH 'total_action_value',
   "ad_id" STRING PATH 'ad_id',
   "unique_clicks" STRING PATH 'unique_clicks'
  ) "xmlTable"
  LEFT JOIN 
   XMLTABLE('/root/data/actions' PASSING JSONTOXML('root',to_chars(w.result,'UTF-8'))
   COLUMNS
   "date_start" STRING PATH '../date_start',
   "ad_id" STRING PATH '../ad_id',
   "action_type" STRING PATH 'action_type',
   "action_value" STRING PATH 'value'
  ) a ON xmlTable.date_start = a.date_start AND xmlTable.ad_id = a.ad_id

  Does this produce the desired output?

   

  Best regards

   

  Niklas

• 1

  Comment actions Permalink

  

  Jonas Seemann

  • October 23, 2018 14:13

  Hi Niklas,

  thank you for your fast response.

  We tried this as well and got the desired results. Easier than we thought :)

  Thanks and BR,

  Jonas

Please sign in to leave a comment.