get unique items from an array (not a range)

[Riesenrind]

I have an excel formula which is collecting data from various sources and putting it into a JSON-style array, e.g. ["apple","banana","banana","orange"] in B2
I want to write another formula which will exclude the duplicates, i.e. return ["apple","banana","orange"].

I have tried converting this to UNIQUE(B2) ... no luck
tried UNIQUE(SUBSTITUTE(SUBSTITUTE(B2,"[","{")"]","}") ... no luck
tried addiitonally subsituting UNIQUE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(B2,"[","{")"]","}",";",",") .... also no luck

Is there any way to do this?
I'm using Excel 365. Because I have a lot of data I would prefer to avoid using VBA or volatile formula

 

[JamesCanale]

Until textsplit comes, use this:

MrExcelPlayground12.xlsx
	A	B
8	"Apple","Banana","Banana","Orange"	"Apple","Banana","Orange"
Sheet12

Cell Formulas
Range	Formula
B8	B8	=TEXTJOIN(",",TRUE,UNIQUE(FILTERXML("<a><s>" &SUBSTITUTE(A8,",","</s><s>") & "</s></a>","//s")))

 

[Riesenrind]

Hey @JamesCanale
Thank you very much for the quick answer. It works well with my example, but not if the last two items are the same
e.g. ["apple","banana","banana","orange"] returns ["apple","banana","banana"]

 

[Fluff]

I'm using Excel 365

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

Another option

="["&TEXTJOIN(",",,UNIQUE(TEXTSPLIT(B2,{"[",",","]"},,1),1))&"]"

 

[Riesenrind]

@JamesCanale
I have extended your solution
=TEXTJOIN(",",TRUE,UNIQUE(FILTERXML("<a><s>" &SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A9,"[","[<s>"),"]","</s>]"),",","</s><s>") & "</s></a>","//s")))
but it is still not quite working. It returns: [],"apple","banana"

 

[JamesCanale]

The brackets were problematic. I removed them and reinstall:

MrExcelPlayground12.xlsx
	A	B
8	["Apple","Orange","Banana","Banana"]	["Apple","Orange","Banana"]
Sheet12

Cell Formulas
Range	Formula
B8	B8	="["&TEXTJOIN(",",TRUE,UNIQUE(FILTERXML("<a><s>" &SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A8,"[",""),"]",""),",","</s><s>") & "</s></a>","//s")))&"]"

 

[Riesenrind]

@JamesCanale Your last proposal worked perfectly. Thank you very much!
@Fluff My version of Excel isnt supporting TEXTSPLIT. But thank you anyway. I took your hint an updated my account into