Parse Timestamp object from stringified JSON in BigQuery

Question

I have a stringified JSON column in BigQuery that looks like this:

{"status":0,"buyEquipment":false,"created":{"_seconds":1603578346,"_nanoseconds":909000000},"total":0,"events":{"1603578346909":{"status":0}},"approvalStatus":0,"userId":"xAdkXoah6LMXI4xy9SZ14NUGJIH3","facilityId":"-MF_DJYYyRKbW4fs91v_","orderSize":0,"facility":{"name":"Data Center ehf.","photo":"-MF_cjRQ3dWW3NRMJC6I","province":"Southern Peninsula","city":"Reybaer"},"priceKWh":0.01}

I am attempting to extract certain values from it like so:

SELECT
JSON_EXTRACT(data, '$.created') AS Date_Created
FROM table

And I get something like this:

{"_seconds":1607095273,"_nanoseconds":847000000}

Because it is clearly nested. I am not familiar with how the database being imported from (Firestore) handles timestamp objects, but this is how it is being imported into BigQuery.

I would like to either

Turn this into a more familiar Timestamp format using a single BigQuery or Standard SQL command if possible,
or if not just extract it to a format where it is an easy conversion to human readable date in BigQuery.

I have tried various BigQuery builtin functions to no avail. Thank you!

user_5221944 · Answer

I would like to ... just extract it to a format where it is an easy conversion to human readable date in BigQuery.

Use below (quite self-explanatory)

select 
  date(timestamp_seconds(cast(json_extract(data, '$.created._seconds') as int64))) AS Date_Created
from table

if applied to sample data in your question - output is

Parse Timestamp object from stringified JSON in BigQuery

1 Answers