How to use regex with JSON_EXTRACT on big query

Question

I have a JSON field on big query table and currently i'm using the following method to do the extraction from a id element (for example):

coalesce(
   nullif(JSON_EXTRACT(e.event_payload, 'content_id'), ''),
   nullif(JSON_EXTRACT(e.event_payload, 'cid'), ''),
   nullif(JSON_EXTRACT(e.event_payload, 'c_id'), ''),
   ...
  ) AS content_id,

I don't have a pattern on this JSON's fields... Is possible use REGEX with JSON_EXTRACT on big query like this?

JSON_EXTRACT(e.event_payload, "(content_id|cid|c_id)") as content_id

No, these keys represent the same information, my problem is that JSON's have different origin, so they do not follow the same pattern... — Gabriel Velho Ghellere
– Gabriel Velho Ghellere, Commented Mar 13, 2019 at 12:32

Elliott Brossard · Accepted Answer · 2019-03-13 13:32:59Z

3

You can replace the possible field names and then perform extraction:

SELECT
  JSON_EXTRACT(
    REGEXP_REPLACE(e.event_payload, r'"c\_?id"', '"content_id"'),
    '$.content_id') as content_id
FROM dataset.table

As a self-contained example:

WITH T AS (
  SELECT '{"cid": {"a": 1}}' AS event_payload UNION ALL
  SELECT '{"content_id": {"b": 2}}' UNION ALL
  SELECT '{"c_id": {"c": 3}}'
)
SELECT
  JSON_EXTRACT(
    REGEXP_REPLACE(e.event_payload, r'"c\_?id"', '"content_id"'),
    '$.content_id') as content_id
FROM T AS e

answered Mar 13, 2019 at 13:32

Elliott Brossard

34k2 gold badges75 silver badges105 bronze badges

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

Comments

SenatorWaffles · Accepted Answer · 2024-04-23 01:45:47Z

0

You can cast your json response to string via JSON_VALUE

eg.

SELECT 

JSON_VALUE(json_specific_column_here,'$.product.en.content')  as stringified_value

FROM

`my-bq-project.my-database.my-datacontent`

This will then let you run string filters like regex over it without the dreaded JSON related type errors.

ref: https://cloud.google.com/bigquery/docs/reference/standard-sql/json_functions#json_value

answered Apr 23, 2024 at 1:45

SenatorWaffles

3892 silver badges7 bronze badges

Collectives™ on Stack Overflow

How to use regex with JSON_EXTRACT on big query

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related