Update value of array field inside JSON string

Question

I have column content in my database that is a type of STRING with JSON structure:

{
    "id": "12",
    "Name": "Ana",
    "lastName": "Johnes",
    "data": [{
            "id": "2",
            "value": "mr"
        }, {
            "id": "3",
            "value": "Ana"
        }
    ]
   
}

Schema of myTable: Content (STRING)

I want to hash and update the 'value' inside the data. I tried using REPLACE function like this but it didn't replace the array "data" with hashed "value":

UPDATE `myTable` set content=
  REPLACE(content, ARRAY_TO_STRING(ARRAY(
      SELECT
        data
      FROM
        UNNEST(json_extract_array(content,
            "$.data"))AS data), ","),ARRAY_TO_STRING(ARRAY(
      SELECT
        REPLACE(data, "\""||json_value(data,
            "$.value")|| "\"", '"'||`hashField`(json_extract_scalar(data,
              '$.value'))||'"' )
      FROM
        UNNEST(json_extract_array(content,
            "$.data")) AS data), ","))
WHERE true

Expected output in column content should look like this:

{
    "id": "12",
    "Name": "Ana",
    "lastName": "Johnes",
    "data": [{
            "id": "2",
            "value": "1256f52fe6125"
        }, {
            "id": "3",
            "value": "6712gf7e1fe76"
        }
    ]
   
}

please clarify - what is expected output - just provide sample output based on already presented input data — Mikhail Berlyant
– Mikhail Berlyant, Commented Aug 18, 2022 at 0:33
expected output would be like this:{ "id": "12", "Name": "Ana", "lastName": "Johnes", "data": [{ "id": "2", "value": "25623g7263d672gd" }, { "id": "3", "value": "67fwevtwe76d7t", } ] } — mini_batman
– mini_batman, Commented Aug 18, 2022 at 0:34
please update your question with this! and where "25623g7263d672gd" and "67fwevtwe76d7t" come from? — Mikhail Berlyant
– Mikhail Berlyant, Commented Aug 18, 2022 at 0:36
that is the result of hash function, my question is how can I update these fields that depend on value of 'value' field — mini_batman
– mini_batman, Commented Aug 18, 2022 at 0:41
The hash function can be SHA1(input), it doesn't really matter, what is important is that I update 'value' — mini_batman
– mini_batman, Commented Aug 18, 2022 at 0:48

Ricco D · Accepted Answer · 2022-08-18 03:21:54Z

2

Consider query below:

with sample_data as (

  select '{"id": "12","Name": "Ana","lastName": "Johnes","data": [{"id": "2","value": "mr"}, {"id": "3","value": "Ana" }] }' as json_string
),

hash_value as (
select 
  struct(
    json_extract_scalar(json_string,'$.id') as id,
    json_extract_scalar(json_string,'$.Name') as Name,
    json_extract_scalar(json_string,'$.lastName') as lastName,
    array(
        select as struct
          json_extract_scalar(arr_data, '$.id') as id,
          sha1(json_extract_scalar(arr_data, '$.value')) as value 
        from unnest(json_query_array(json_string,'$.data')) as arr_data) as data
        ) as json_struct

from sample_data
)

select 
  to_json_string(json_struct) as json_string 
from hash_value

Output:

UPDATE (Add UPDATE statement)

NOTE: UPDATE needs a WHERE statement to determine what data point to update. Since you did not present any unique column, I added a column id so I can use this a reference for the UPDATE statement.

update `your-project.your_dataset.your_table`
set json_string = new_json_string
from (
with hash_value as (
select 
  struct(
    json_extract_scalar(json_string,'$.id') as id,
    json_extract_scalar(json_string,'$.Name') as Name,
    json_extract_scalar(json_string,'$.lastName') as lastName,
    array(
        select as struct
          json_extract_scalar(arr_data, '$.id') as id,
          sha1(json_extract_scalar(arr_data, '$.value')) as value 
        from unnest(json_query_array(json_string,'$.data')) as arr_data) as data
        ) as json_struct

from `your-project.your_dataset.your_table`
)

select 
  to_json_string(json_struct) as new_json_string 
from hash_value
)
where id = 111

edited Aug 18, 2022 at 3:21

answered Aug 18, 2022 at 1:22

Ricco D

7,3451 gold badge10 silver badges19 bronze badges

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

4 Comments

Jaytiger Over a year ago

Excellent and I think it would be better if you use Standard JSON extraction functions instead of legacy ones. cloud.google.com/bigquery/docs/reference/standard-sql/…

Ricco D Over a year ago

@Jaytiger Oh I see. Thanks for the suggestion. Will definitely apply this on my queries.

mini_batman Over a year ago

Thank you for your answer! The result should definitely look like this, but is there a way to use UPDATE on this table so I can change the value of this field?

Ricco D Over a year ago

@mini_batman I updated my answer to add the UPDATE statement

Mikhail Berlyant · Accepted Answer · 2022-08-18 02:10:21Z

0

The hash function can be SHA1(input), it doesn't really matter, what is important is that I update 'value'

Based on above statement, I assume you just really want to mask real values, the replacement value really do not matter - if so consider below naïve and super simple approach

select regexp_replace(Content, r'"value":\s*"([^"]+)"', '"value": "XXX')
from your_table

if applied to sample data in your question - output (of that column) is

And if you need to update that field in the table - you can simply use

update your_table
set Content = regexp_replace(Content, r'"value":\s*"([^"]+)"', '"value": "XXX')
where true

answered Aug 18, 2022 at 2:10

Mikhail Berlyant

174k10 gold badges173 silver badges251 bronze badges

1 Comment

mini_batman Over a year ago

Thank you for your answer! But I would still like to have hash depending on the value. This will replace every value with the same hashing value.

Collectives™ on Stack Overflow

Update value of array field inside JSON string

2 Answers 2

4 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

4 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related