J'ai une table avec la chaîne json UserID json_string 100 [{"id": 77379513, "value": "35.4566", "os_type": null, "amount": "200", "...

1
Gross 14 mars 2021 à 15:58

1 réponse

Meilleure réponse

Ci-dessous fonctionnera pour vous

select UserID, 
  json_extract_scalar(json, '$.id') as id,
  json_extract_scalar(json, '$.value') as value,
  json_extract_scalar(json, '$.os_type') as os_type,
  json_extract_scalar(json, '$.amount') as amount,
  json_extract_scalar(json, '$.created_at') as created_at,
  json_extract_scalar(json, '$.updated_at') as updated_at,
  json_extract_scalar(json, '$.Type_name') as Type_name
from `project.dataset.table`,
unnest(json_extract_array(json_string, '$')) json       

Si appliquer à des exemples de données dans votre question

with `project.dataset.table` as (
  select 100 UserID, '[{"id": 77379513, "value": "35.4566", "os_type": null, "amount": "200", "created_at": "2020-08-16T14:48:27.611-04:00", "updated_at": "2020-08-16T14:48:27.836-04:00", "Type_name": "same"}]' json_string union all
  select 100, '[{"id": 77379514, "value": "38.658", "os_type": null, "amount": "100", "created_at": "2020-08-16T14:48:27.611-04:00", "updated_at": "2020-08-16T14:48:27.836-04:00", "Type_name": "niko"}]' union all
  select 100, '[{"id": 77379515, "value": "40.569", "os_type": null, "amount": "150", "created_at": "2020-08-16T14:48:27.611-04:00", "updated_at": "2020-08-16T14:48:27.836-04:00", "Type_name": "koko"}]' union all
  select 200, '[{"id": 77378899, "value": "25.365", "os_type": null, "amount": "100", "created_at": "2020-08-16T14:48:27.611-04:00", "updated_at": "2020-08-16T14:48:27.836-04:00", "Type_name": "same"}]' union all
  select 200, '[{"id": 77378900, "value": "35.898", "os_type": null, "amount": "500", "created_at": "2020-08-16T14:48:27.611-04:00", "updated_at": "2020-08-16T14:48:27.836-04:00", "Type_name": "niko"}]' union all
  select 200, '[{"id": 77378901, "value": "41.258", "os_type": null, "amount": "400", "created_at": "2020-08-16T14:48:27.611-04:00", "updated_at": "2020-08-16T14:48:27.836-04:00", "Type_name": "koko"}]' 
)

La sortie est

enter image description here

Remarque: vous avez utilisé ' à quelques endroits au lieu de ", c'est donc "corrigé" dans les exemples de données utilisés ci-dessus

Si vous n'avez pas de contrôle sur la valeur de ce tableau et que vous ne pouvez pas corriger ' en ", vous pouvez utiliser ci-dessous à la place

select UserID, 
  json_extract_scalar(json, '$.id') as id,
  json_extract_scalar(json, '$.value') as value,
  json_extract_scalar(json, '$.os_type') as os_type,
  json_extract_scalar(json, '$.amount') as amount,
  json_extract_scalar(json, '$.created_at') as created_at,
  json_extract_scalar(json, '$.updated_at') as updated_at,
  json_extract_scalar(json, '$.Type_name') as Type_name
from `project.dataset.table`,
unnest(json_extract_array(replace(json_string, "'", '"'), '$')) json 

Notez le changement dans unnest qui résout ce problème avec '

1
Mikhail Berlyant 14 mars 2021 à 18:54