Parse JSON formatted field to create multiple records

[kthibeault1965]

Hello Everyone

We are using a SQL Active Link table to read orders being created in a web ecommerce platform. The data is stored in MySQL, and the structures is very flat.

ONE ORDER = ONE ROW

Essentially they have merged the typical ORDER HEADER table and ORDER DETAIL table into one ORDER table.

The storage of the detail data including ITEM, QTY, PRICE, DESC, etc is contained in one MEMO field.
We need to write a script that reads the memo field data into a variable, parses the data in the variable, and creates a new ORDER DETAIL row with each new product ordered.

The Json legend of formatting is at http://www.json.org/

the Array is shown via [ ]
The Object is shown via { }

The data example below should create 3 separate records

[{"rowid":"3a3d205e3f77d0d10822a2a7e7acf83e","id":"5","qty":1,"price":"35.95","name":"Magic Panty","options":{"color":"Bianco","size":"ML","sku":"8300496004851"},"subtotal":"35.95"},{"rowid":"321578152208d3d15681a497403ed26c","id":"5","qty":1,"price":"35.95","name":"Magic Panty","options":{"color":"Noisette (Hazelnut)","size":"M","sku":"8300496004943"},"subtotal":"35.95"},{"rowid":"c0ab5b5101bbb85339ce1a73f0e7dcb5","id":"5","qty":1,"price":"35.95","name":"Magic Panty","options":{"color":"Nero (Black)","size":"M","sku":"8300496005117"},"subtotal":"35.95"}]

[Stan Mathews]

Re: Parse JSON formatted field to create multiple records

See it these experiments get you started.

tbl = table.open("dtbl")
tst = tbl.DT
? tst
= [{"rowid":"3a3d205e3f77d0d10822a2a7e7acf83e","id":"5","qty":1,"price":"35.95","name":"Magic Panty","options":{"color":"Bianco","size":"ML","sku":"8300496004851"},"subtotal":"35.95"},{"rowid":"321578152208d3d15681a497403ed26c","id":"5","qty":1,"price":"35.95","name":"Magic Panty","options":{"color":"Noisette (Hazelnut)","size":"M","sku":"8300496004943"},"subtotal":"35.95"},{"rowid":"c0ab5b5101bbb85339ce1a73f0e7dcb5","id":"5","qty":1,"price":"35.95","name":"Magic Panty","options":{"color":"Nero (Black)","size":"M","sku":"8300496005117"},"subtotal":"35.95"}]

ntest = strtran(tst,"{\"row",crlf()+"{\"row")
? ntest
= [
{"rowid":"3a3d205e3f77d0d10822a2a7e7acf83e","id":"5","qty":1,"price":"35.95","name":"Magic Panty","options":{"color":"Bianco","size":"ML","sku":"8300496004851"},"subtotal":"35.95"},
{"rowid":"321578152208d3d15681a497403ed26c","id":"5","qty":1,"price":"35.95","name":"Magic Panty","options":{"color":"Noisette (Hazelnut)","size":"M","sku":"8300496004943"},"subtotal":"35.95"},
{"rowid":"c0ab5b5101bbb85339ce1a73f0e7dcb5","id":"5","qty":1,"price":"35.95","name":"Magic Panty","options":{"color":"Nero (Black)","size":"M","sku":"8300496005117"},"subtotal":"35.95"}]
nntest = ""
cnt = w_count(ntest,crlf())
? cnt
= 4
for i = 2 to cnt
#nntest = nntest+word(ntest,i,crlf())+crlf()
#next i
? nntest
= {"rowid":"3a3d205e3f77d0d10822a2a7e7acf83e","id":"5","qty":1,"price":"35.95","name":"Magic Panty","options":{"color":"Bianco","size":"ML","sku":"8300496004851"},"subtotal":"35.95"},
{"rowid":"321578152208d3d15681a497403ed26c","id":"5","qty":1,"price":"35.95","name":"Magic Panty","options":{"color":"Noisette (Hazelnut)","size":"M","sku":"8300496004943"},"subtotal":"35.95"},
{"rowid":"c0ab5b5101bbb85339ce1a73f0e7dcb5","id":"5","qty":1,"price":"35.95","name":"Magic Panty","options":{"color":"Nero (Black)","size":"M","sku":"8300496005117"},"subtotal":"35.95"}]

ccnt = w_count(word(nntest,1,crlf()),",")
? ccnt
= 9

for i = 1 to 9
#? word(word(nntest,1,crlf()),i,",")
#next i
{"rowid":"3a3d205e3f77d0d10822a2a7e7acf83e"
"id":"5"
"qty":1
"price":"35.95"
"name":"Magic Panty"
"options":{"color":"Bianco"
"size":"ML"
"sku":"8300496004851"}
"subtotal":"35.95"}

[kthibeault1965]

Re: Parse JSON formatted field to create multiple records

Thanks Stan! will try em out!

[Stan Mathews]

Re: Parse JSON formatted field to create multiple records

The options "field" will require a little extra coding since it doesn't follow the pattern exactly.

"options":{"color":"Bianco"

I'd have to know what might be found here for options and what you would want to do with it to say more.

[kthibeault1965]

Re: Parse JSON formatted field to create multiple records

yeah.. this options field is a weird one. the vision was to have the ability to have MULTIPLE numbers of options per product.
BUT
the reality is that OPTIONS should be thrown away, and just
COLOR, SIZE, and SKU should be added to the detail table. playing with the scripts now.

[Stan Mathews]

Re: Parse JSON formatted field to create multiple records

tbl = table.open("dtbl") 'the table holding the data
tst = tbl.dt 'dt is the memo field
tbl.close()
ntest = strtran(tst,"{\"row",crlf()+"{\"row")
nntest = ""
cnt = w_count(ntest,crlf())
FOR i = 2 TO cnt
	nntest = nntest+word(ntest,i,crlf())+crlf()
NEXT i
lines = w_count(nntest,crlf())
ccnt = w_count(word(nntest,1,crlf()),",")
a_tbl = table.open("order") ' the table to write the orders to
FOR l = 1 TO lines
	FOR i = 1 TO ccnt
		IF left(word(word(nntest,l,crlf()),i,","),5) = quote("sku")
			a_tbl.enter_begin()
			a_tbl.Sku = unquote(word(word(word(nntest,l,crlf()),i,","),2,":"))
			a_tbl.enter_end(.t.)
		END IF
	NEXT i
	FOR i = 1 TO ccnt
		IF left(word(word(nntest,l,crlf()),i,","),6) = quote("size")
			a_tbl.change_begin()
			a_tbl.Size = unquote(word(word(word(nntest,l,crlf()),i,","),2,":"))
			a_tbl.change_end(.t.)
		else if	left(word(word(nntest,l,crlf()),i,","),9) = quote("options")
			a_tbl.change_begin()
			a_tbl.Color = unquote(word(word(word(nntest,l,crlf()),i,","),3,":"))
			a_tbl.change_end(.t.)
		END IF
	NEXT i
NEXT l
a_tbl.close()

[kthibeault1965]

Re: Parse JSON formatted field to create multiple records

This works great, except, what is the approach to use to fill in ALL the data fields, not just the SKU, SIZE, and COLOR?
I have played with the script with no progress unfortunately... Any ideas? We want to have one row per rowid, and all data elements filled in. cheers

[Stan Mathews]

Re: Parse JSON formatted field to create multiple records

tbl = table.open("dtbl") 'the table holding the data
tst = tbl.dt 'dt is the meme field
tbl.close()
ntest = strtran(tst,"{\"row",crlf()+"{\"row")
nntest = ""
cnt = w_count(ntest,crlf())
FOR i = 2 TO cnt
	nntest = nntest+word(ntest,i,crlf())+crlf()
NEXT i
lines = w_count(nntest,crlf())
ccnt = w_count(word(nntest,1,crlf()),",")
a_tbl = table.open("order")
FOR l = 1 TO lines
	FOR i = 1 TO ccnt
		IF left(word(word(nntest,l,crlf()),i,","),5) = quote("sku")
			a_tbl.enter_begin()
			a_tbl.Sku = unquote(word(word(word(nntest,l,crlf()),i,","),2,":"))
			a_tbl.enter_end(.t.)
		END IF
	NEXT i
	FOR i = 1 TO ccnt
		IF left(word(word(nntest,l,crlf()),i,","),6) = quote("size")
			a_tbl.change_begin()
			a_tbl.Size = unquote(word(word(word(nntest,l,crlf()),i,","),2,":"))
			a_tbl.change_end(.t.)
		else if substr(word(word(nntest,l,crlf()),i,","),2,7) = quote("rowid")
			a_tbl.change_begin()
			a_tbl.Rowid = unquote(word(word(word(nntest,l,crlf()),i,","),2,":"))
			a_tbl.change_end(.t.)
		else if left(word(word(nntest,l,crlf()),i,","),4) = quote("id")
			a_tbl.change_begin()
			a_tbl.Id = unquote(word(word(word(nntest,l,crlf()),i,","),2,":"))
			a_tbl.change_end(.t.)
		else if left(word(word(nntest,l,crlf()),i,","),5) = quote("qty")
			a_tbl.change_begin()
			a_tbl.Qty = word(word(word(nntest,l,crlf()),i,","),2,":")
			a_tbl.change_end(.t.)
		else if left(word(word(nntest,l,crlf()),i,","),7) = quote("price")
			a_tbl.change_begin()
			a_tbl.Price = unquote(word(word(word(nntest,l,crlf()),i,","),2,":"))
			a_tbl.change_end(.t.)
		else if left(word(word(nntest,l,crlf()),i,","),6) = quote("name")
			a_tbl.change_begin()
			a_tbl.Name = unquote(word(word(word(nntest,l,crlf()),i,","),2,":"))
			a_tbl.change_end(.t.)	
		else if left(word(word(nntest,l,crlf()),i,","),10) = quote("subtotal")
			a_tbl.change_begin()
			a_tbl.Subtotal = unquote(word(word(word(nntest,l,crlf()),i,","),2,":"))
			a_tbl.change_end(.t.)
		else if	left(word(word(nntest,l,crlf()),i,","),9) = quote("options")
			a_tbl.change_begin()
			a_tbl.Color = unquote(word(word(word(nntest,l,crlf()),i,","),3,":"))
			a_tbl.change_end(.t.)
		END IF
	NEXT i
NEXT l
a_tbl.close()

Assuming all character type fields in the order table.

[Stan Mathews]

Re: Parse JSON formatted field to create multiple records

If you know that all fields will be represented, this would be much quicker.

tbl = table.open("dtbl") 'the table holding the data
tst = tbl.dt 'dt is the meme field
tbl.close()
ntest = strtran(tst,"{\"row",crlf()+"{\"row")
nntest = ""
cnt = w_count(ntest,crlf())
FOR i = 2 TO cnt
	nntest = nntest+word(ntest,i,crlf())+crlf()
NEXT i
lines = w_count(nntest,crlf())
ccnt = w_count(word(nntest,1,crlf()),",")
a_tbl = table.open("order")
FOR l = 1 TO lines
	a_tbl.enter_begin()
	a_tbl.Sku = unquote(word(word(word(nntest,l,crlf()),8,","),2,":"))
	a_tbl.enter_end(.t.)
	a_tbl.change_begin()
	a_tbl.Size = unquote(word(word(word(nntest,l,crlf()),7,","),2,":"))
	a_tbl.Rowid = unquote(word(word(word(nntest,l,crlf()),1,","),2,":"))
	a_tbl.Id = unquote(word(word(word(nntest,l,crlf()),2,","),2,":"))
	a_tbl.Qty = word(word(word(nntest,l,crlf()),3,","),2,":")
	a_tbl.Price = unquote(word(word(word(nntest,l,crlf()),4,","),2,":"))
	a_tbl.Name = unquote(word(word(word(nntest,l,crlf()),5,","),2,":"))
	a_tbl.Subtotal = unquote(word(word(word(nntest,l,crlf()),9,","),2,":"))
	a_tbl.Color = unquote(word(word(word(nntest,l,crlf()),6,","),3,":"))
	a_tbl.change_end(.t.)
NEXT l
a_tbl.close()

[kthibeault1965]

Re: Parse JSON formatted field to create multiple records

IT WORKED !!!! PROBLEM SOLVED !!!

thanks Stan ! you rock. Some very advanced stuff with word() and handling options in this data set

[Stan Mathews]

Re: Parse JSON formatted field to create multiple records

I don't know how advanced it really is. You just have to recognize the patterns in the data structure and work out how to separate the whole into pieces that have the actual data you need.

Tedious to construct at times but Alpha has the tools you need.