-1

We have Json Variant column in the table. The column D has json variant value like this:

[
   "[{\"xyz_id\":0001,\"abc_id\":10032,\"dis_name\":\" AP 20%\",\"dis_type_name\":\"Subtotal Dis\",\"disc_rate\":20.0,\"discount_total\":-1.0000}]"
]

We want to Create new Column E as xyz_id in that Column. we need to Strip out this values ()

Azhar Khan
  • 3,829
  • 11
  • 26
  • 32
kartik vyas
  • 1
  • 2
  • Take a moment to read through the [editing help](/editing-help) in the help center. Formatting on Stack Overflow is different than on other sites. The better your post looks, the easier it is for others to read and understand it. – Jim G. Jan 27 '23 at 19:38

1 Answers1

1

Is that a valid JSON sample you've posted as copied from Snowflake? I'm not to sure how to interpret it. If I strip the outer [" and "] the below code can be used to extract the field your looking for.

select parse_json('[{"xyz_id":0001,"abc_id":10032,"dis_name":" AP 20%","dis_type_name":"Subtotal Dis","disc_rate":20.0,"discount_total":-1.0000}]') COL_D,
       COL_D[0]:"xyz_id" COL_E;
Fieldy
  • 436
  • 1
  • 4
  • Thank you for your suggestion !! The data posted is correct. This data we are having in internal assigments. The Column D have Variant like this - [ "[{\"xyz_id\":0001,\"abc_id\":10032,\"dis_name\":\" AP 20%\",\"dis_type_name\":\"Subtotal Dis\",\"disc_rate\":20.0,\"discount_total\":-1.0000}]" ] Please help me with; Do we need to remove (\) or what would be the code be create the Column E with xyz_id. – kartik vyas Jan 27 '23 at 04:22
  • Please update your question with minimum reproducible example to create the table containing your variant data. – Fieldy Jan 27 '23 at 12:02