Kenny Gorman kgorman

Python - comparing JSON data structures

A function compare_json_data(source_data_a,source_data_b), accepting structures populated with data loaded from json.load() and comparing for equality.

Example

$ ./compare.py 
Compare JSON result is: True

	SELECT
	userid,
	SUM(amount) AS theamount,
	tumble_end(timestamp, interval '1' hour) AS ending_ts
	FROM mystream
	GROUP BY userid, tumble(timestamp, interval '1' hour)

	{"userid": 100, "amount": 10000, "timestamp": 1618852483}
	{"userid": 200, "amount": 23000, "timestamp": 1618852483}
	{"userid": 100, "amount": 4000, "timestamp": 1618848883}
	{"userid": 200, "amount": 300, "timestamp": 1618848883}

	// hello world
	function HELLOWORLD() {
	return "Hello World";
	}
	HELLOWORLD();

	-- return aggregation of total payments
	-- over a 1 hour tumbling window
	SELECT SUM(CAST(amount AS numeric)) AS payment_volume,
	CAST(TUMBLE_END(eventTimestamp, interval '1' hour) AS varchar) AS ts
	FROM payments
	GROUP BY TUMBLE(eventTimestamp, interval '1' hour);

	-- eventTimestamp is the Kafka timestamp
	-- as unix timestamp. Magically added to every schema.
	SELECT max(eventTimestamp) FROM solar_inputs;

	-- make it human readable
	SELECT CAST(max(eventTimestamp) AS varchar) as TS FROM solar_inputs;

	-- dete math with interval
	SELECT * FROM payments
	WHERE eventTimestamp > CURRENT_TIMESTAMP-interval '10' second;

	{"userid": 100, "theamount": 14000, "ending_ts": 1618848883}
	{"userid": 200, "theamount": 23300, "ending_ts": 1618848883}

	-- detect multiple auths in a short window and
	-- send to lock account topic/microservice
	SELECT card,
	MAX(amount) as theamount,
	TUMBLE_END(eventTimestamp, interval '5' minute) as ts
	FROM payments
	WHERE lat IS NOT NULL
	AND lon IS NOT NULL
	GROUP BY card, TUMBLE(eventTimestamp, interval '5' minute)
	HAVING COUNT(*) > 4 -- >4==fraud

	-- new to my airspace
	SELECT icao, pings FROM (
	SELECT icao, speed,
	COUNT(*) OVER (PARTITION BY icao ORDER BY eventTimestamp RANGE BETWEEN INTERVAL '60' SECOND PRECEDING AND CURRENT ROW) AS pings
	FROM airplanes
	) WHERE pings < 5
	AND icao like 'A%'