Read and aggregate Json data

karthigaimuthu

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,

Jim_Knicely

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)