Read and aggregate Json data
Hi ,
From this sample record provided I want to group by productid and device_type . In some places the device_meta which is stored as json of type varchar is empty/None.
I tried below to group by the device_type/device_os but I unable to extract the productid and apply group by along with this result.
SELECT keys, values , count(1) FROM
(SELECT C.keys, C.values
FROM ( SELECT MAPITEMS(MAPJSONEXTRACTOR(B.device_meta)) over()
FROM ( SELECT REPLACE(REPLACE(A.device_meta, '''','"'),'None','"None"') device_meta
FROM ai_schema.click_data_34355353 A
WHERE client=34355353 and type='purchase' and transactiondate between '2021-10-01 00:00:00' and '2021-12-01 23:59:59'
) B
) AS C
WHERE C.values <> ''
) D
WHERE D.keys = 'device_type'
GROUP BY 1,2;
Sample raw data in the table
client | 34355353
token_id | 000152f8
customerid | 000152f8
productid | P00002323
transactiondate | 2021-10-01 20:05:07.961
widget | PLP-Standard-Recommendations
user_segment | _default
experiment | None
user_type | new
private_model_version | 602
public_model_version | airgsp
pagename | PLP
rrid | None
device_meta | {'client_type': 'browser', 'client_name': 'Chrome Mobile iOS', 'device_os': 'iOS', 'device_type': 'smartphone'}
instance | None
stamp | 1633118707.961
msp | 0
position | 22
date |
vid | 8f3c1d5b-d6c8
record_hash | 4818b2261c556af543ae2a4db54c96a
type | purchase
Also , clarify if we can join another table using productid in the above query,
Answers
Seems ok. Except, for the sample data, the TYPE = "clicked"...
dbadmin=> SELECT * FROM ai_schema.click_data_34355353; client | token_id | customerid | productid | transactiondate | widget | user_segment | experiment | user_type | private_model_version | public_model_version | pagename | rrid | device_meta | instance | stamp | msp | position | date | vid | record_hash | type ----------+--------------------------------------+--------------------------------------+-----------+-------------------------+------------------------------+--------------+------------+-----------+-----------------------+----------------------+----------+------+-----------------------------------------------------------------------------------------------------------------+----------+-----------------------+-----+----------+------+--------------------------------------+------------------------------------------------------------------+--------- 34355353 | 000152f8-221d-42c4-b863-c147ce36d730 | 000152f8-221d-42c4-b863-c147ce36d730 | P00002323 | 2021-10-01 20:05:07.961 | PLP-Standard-Recommendations | _default | None | new | 602 | airgsp | PLP | None | {'client_type': 'browser', 'client_name': 'Chrome Mobile iOS', 'device_os': 'iOS', 'device_type': 'smartphone'} | None | 1633118707.9610000000 | 0 | 22 | | 8f3c1d5b-d6c8-4864-9898-fc3b7cb4bfb6 | 4818b2261c556af543ae2a4db54c96a21787a49c3dce05c649b9c5d879ba2561 | clicked (1 row) dbadmin=> SELECT keys, values , count(1) FROM dbadmin-> (SELECT C.keys, C.values dbadmin(> FROM ( SELECT MAPITEMS(MAPJSONEXTRACTOR(B.device_meta)) over() dbadmin(> FROM ( SELECT REPLACE(REPLACE(A.device_meta, '''','"'),'None','"None"') device_meta dbadmin(> FROM ai_schema.click_data_34355353 A dbadmin(> WHERE client=34355353 and type='clicked' and transactiondate between '2021-10-01 00:00:00' and '2021-12-01 23:59:59' dbadmin(> ) B dbadmin(> ) AS C dbadmin(> WHERE C.values <> '' dbadmin(> ) D dbadmin-> WHERE D.keys = 'device_type' dbadmin-> GROUP BY 1,2; keys | values | count -------------+------------+------- device_type | smartphone | 1 (1 row)