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

Question

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.

I would guess that is because it is an array of objects!? I think it would need to look something like SELECT JSON_VALUE(evaluation,'$[0].Score') — user8103263
– user8103263, Commented Aug 28, 2021 at 11:30

Gordon Linoff · Accepted Answer · 2021-08-28 11:53:23Z

1

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

answered Aug 28, 2021 at 11:53

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

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

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related