CODEWITHSUNDEEP
sqlgoogle-bigquery
Azubike
Azubike

Reputation: 1

How to extract a value from a json string in Big Query using SQL

How can I extract a value from this string below using Big Query? (this is a sample from a field called, 'evaluation')

[{"Id": "abcdef", "evaluationId": "1234567890", "createDate": "2021-08-16T14:42:42", "evaluationDate": "2021-08-19T14:44:42", "evaluationClass": "ADVANCED", "Score": 500, "evaluationToken": null, "evaluationStatus": "ACTIVE"}]

For example, I'd like to extract the value, 'Score'. I've tried JSON functions, like:

SELECT JSON_VALUE(evaluation,'$.Score')

But this just returns a null value.

Upvotes: 0

Views: 6834

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269443

This is because the JSON is an array, so you need to fetch it as an array:

select JSON_VALUE(evaluation,'$[0].Score')
from (select '[{"Id": "abcdef", "evaluationId": "1234567890", "createDate": "2021-08-16T14:42:42", "evaluationDate": "2021-08-19T14:44:42", "evaluationClass": "ADVANCED", "Score": 500, "evaluationToken": null, "evaluationStatus": "ACTIVE"}]' as evaluation) t;

I suppose that if you did not intend for it to be an array, you could remove the first and last characters and use your access path:

select JSON_VALUE(substr(evaluation, 2, length(evaluation) - 2),'$.Score')
from (select '[{"Id": "abcdef", "evaluationId": "1234567890", "createDate": "2021-08-16T14:42:42", "evaluationDate": "2021-08-19T14:44:42", "evaluationClass": "ADVANCED", "Score": 500, "evaluationToken": null, "evaluationStatus": "ACTIVE"}]' as evaluation) t

Upvotes: 1

Related Questions