DELETE data from table, WHERE a field in JSON column

• sirkinghorse

  SSC-Addicted

  Points: 457

  More actions

  July 26, 2018 at 2:18 am

  #373810

  Hi,

  I have data in table "FileTable" with a json field, in that field there are values:
  { "File":"Daily", "FileNumber":01, "FileType":"DO"}
{ "File":"Monthly", "FileNumber":02, "FileType":"DO"}

  I want to Delete data from FileTable but where "FileType" = "DO"

  Please help.

• debasis.yours

  Hall of Fame

  Points: 3549

  More actions

  July 26, 2018 at 2:41 am

  #1999044

  Hi,

  As I tried to replicate your JSON column values in one of my tables, but there is a flaw here. You will need to enclose the FileNumber part with double-quote as well (e.g. { "File":"Daily", "FileNumber":"01", "FileType":"DO"}).

  Otherwise it will throw error.

  Then you can use the following query to delete:
  DELETE dbo.<YourTableName> WHERE JSON_VALUE(<YourColumnName>, '$.FileType') = 'DO'

  Please let me know.