http://coldfusion.com logo
Join Slack
Powered by
Silly question here. I am building dynamic values ...
# cfml-beginners
e
Silly question here. I am building dynamic values in json file Based on value of complete_refund, I need to make value of shipping_refund as 
"amount": 0
OR 
"full_refund": true
I can't seem to make it work. 
<cfif complete_refund is "TRUE">
<cfset shipping_refund = "full_refund": &true>
<cfelse>
<cfset shipping_refund = "amount": &0>
</cfif>
What am I doing wrong?
a
Firstly... I recommend not building JSON by hand. Build a struct, and then convert it to JSON once yer ready to send it to whatever needs the JSON. Secondly... you have posted some code there but haven't said what's wrong with it. What is it about that code that doesn't work?
☝️ 1
e
image.png
m
you seem to be attempting to use a colon 
:
as an operator. Should it be part of the string?
✅ 1
e
image.png
a
you seem to be attempting to use a colon 
:
as an operator
Ha, didn't spot that. Indeed. The picture of the code above is different though, and has the colon in the string correctly. BTW: pls never paste pictures of code. Post the actual code!
m
@epipko, is the last screenshot an updated version? Is it still throwing an error?
e
still erroring out
m
with the same message you included above?
e
my bad, this code shows Shipping Refund: amount:0 
<cfif complete_refund is "TRUE">
<cfset shipping_refund = "full_refund:" &true>
<cfelse>
<cfset shipping_refund = "amount:" &0>
</cfif>
<cfoutput>Shipping Refund: #shipping_refund#</cfoutput><cfabort>
m
So..problem solved?
e
It needs to be: "amount": 0
a
@epipko when quoting a block of code, start the formatting by typing three back-ticks.
Copy code
multiple
lines
all
nicely
formatted
Like I said... stop trying to build JSON by hand! This is not how to solve whatever it is you are trying to solve.
🎯 1
e
Copy code
<cfif complete_refund is "TRUE">
	<cfset shipping_refund = "full_refund:" &true>	
<cfelse>
	<cfset shipping_refund = "amount:" &0>
</cfif>
<cfoutput>Shipping Refund: #shipping_refund#</cfoutput><cfabort>
⭐ 1
i
Change "amount:" &0 to '"amount:"0' if it needs to be "amount:" 0. If you want to output doublequotes enclose them in single quotes like here. '"amount:"0' Also, Adam is 100% correct. don't build json by hand. if you have
Copy code
json = { amount: 0 }, and call writeOutput(serializeJson(json)); it will be formatted for you.
☝️ 1
e
Thank you all, I ended up writing this and it works:
Copy code
<cfif complete_refund is "TRUE">
	<cfset shipping_refund_str = '"full_refund": true'>	
<cfelse>
	<cfset shipping_refund_str = '"amount": 0'>
</cfif>

<cfset shipping_refund = deserializeJSON('{' & shipping_refund_str & '}')>
m
Wait, if you need JSON, why are you deserializing anything?
🤦‍♀️ 1
e
I am doing some processing and building values that I am later turn into a valid JSON
Copy code
<cfquery name="q0" datasource="#REQUEST.dsn#">	
	select LISTAGG('{"line_item_id": '||order_item_code||' ,"quantity": '||sales_qty||', "restock_type": "return"}',',') line_items 
	from ep
	where orig_order_id = '#orig_order_id#'
</cfquery>

<cfset line_items_str = #q0.line_items#>
<cfset line_items_array = deserializeJSON( '[' & line_items_str & ']' )>


<cfif complete_refund is "TRUE">
	<cfset shipping_refund_str = '"full_refund": true'>	
<cfelse>
	<cfset shipping_refund_str = '"amount": 0'>
</cfif>

<cfset shipping_refund = deserializeJSON('{' & shipping_refund_str & '}')> 

<cfset json_str = 
	{
		"refund": {
		  "shipping": shipping_refund
		  ,"refund_line_items": 
		  	line_items_array
		}
	}  
>

<cfset _PAYLOAD = serializeJSON(json_str)>

<cfoutput>#_PAYLOAD#</cfoutput><cfabort>
this returns : {"refund":{"refund_line_items":[{"quantity":1,"line_item_id":12273697456307,"restock_type":"return"},{"quantity":1,"line_item_id":12273697489075,"restock_type":"return"}],"shipping":{"full_refund":true}}}
image.png
a
yeah, nah: that's not how to do that. Like I said - beginning to wonder if this thing is switched on... let's see if it works if I shout: STOP HAND-BUILDING JSON
You only need this:
Copy code
<cfif complete_refund is "TRUE">
	<cfset shipping_refund = {"full_refund" = true}>
<cfelse>
	<cfset shipping_refund = {"amount" = 0}>
</cfif>
Also you should not hard-code 
orig_order_id
into your SQL string: pass it as a parameter.
And don't need the pound-signs on this: 
#q0.line_items#
e
Thank you Adam for links.
✅ 1
Adam, are you saying that I did not have to build a string and then turn it into JSON even for line_items_array ? I followed your advise and instead of this code:
Copy code
<cfquery name="q0" datasource="#REQUEST.jesta_PROD#">	
	select LISTAGG('{"line_item_id": '||order_item_code||' ,"quantity": '||sales_qty||', "restock_type": "return"}',',') line_items 
	from ep
	where orig_order_id = '#orig_order_id#'
</cfquery>

<cfset line_items_str = q0.line_items>
<cfset line_items_array = deserializeJSON( '[' & line_items_str & ']' )>
ran this:
Copy code
<cfquery name="q0" datasource="#REQUEST.jesta_PROD#">	
	select LISTAGG('{"line_item_id": '||order_item_code||' ,"quantity": '||sales_qty||', "restock_type": "return"}',',') line_items 
	from ep
	where orig_order_id = '#orig_order_id#'
</cfquery> 

<cfset line_items_array = '[' & q0.line_items & ']'>

<cfif complete_refund is "TRUE">
	<cfset shipping_refund = {"full_refund" = true}>
<cfelse>
	<cfset shipping_refund = {"amount" = 0}>
</cfif>

<cfset _PAYLOAD = serializeJSON(json_str)>
<cfoutput>#_PAYLOAD#</cfoutput><cfabort>
It looks like it worked and the only difference is that now json is with "\" in it. It looks like it's still valid, just looks different. Do I need to care?
Copy code
{
  "refund": {
    "refund_line_items": "[{\"line_item_id\": 12273697456307 ,\"quantity\": 1, \"restock_type\": \"return\"},{\"line_item_id\": 12273697489075 ,\"quantity\": 1, \"restock_type\": \"return\"}]",
    "shipping": {
      "full_refund": true
    }
  }
}
🎯 1
a
Yeah cos yer still building the JSON by hand, just in a different place: 
<cfset line_items_array = deserializeJSON( '[' & line_items_str & ']' )>
Stop it. Don't do it. I don't know how else to say it. DO NOT BUILD JSON BY HAND. 
refund_line_items
is supposed to be an array, right? You've given it a string. As well as everything else you need to look at what the code is doing, and understand it? Clearly(?) 
"refund_line_items": "[{\"line_item_id\": 12273697456307 ,\"quantity\": 1, \"restock_type\": \"return\"},{\"line_item_id\": 12273697489075 ,\"quantity\": 1, \"restock_type\": \"return\"}]
is wrong. Well: unless you actually do want that to be a string in the JSON object, but I really doubt it.
Also yer only sharing dribs and drabs of the code, and it's virtually impossible to guess what yer doing. What are you doing here?
Copy code
select LISTAGG('{"line_item_id": '||order_item_code||' ,"quantity": '||sales_qty||', "restock_type": "return"}',',') line_items
Looks like yer building a partial JSON string there too. Just return the values. Stick the values into some sort of data structure. Convert the data structure to JSON last (and only once) before you - I guess - return it to the client.
e
I am building a string from database values. The query you're referring to returns: {"line_item_id": 12273697456307 ,"quantity": 1, "restock_type": "return"},{"line_item_id": 12273697489075 ,"quantity": 1, "restock_type": "return"}
a
Yes, I know. But you don't need. You could just return the values, right? As separate rows. Of values.
This is all part of "stop building JSON by hand"
e
hmm...let me try
a
If you didn't have the 
LISTAGG
function in there, you'd just get a nice recordset along these lines wouldn't you?
Copy code
line_item_id, quantity, restock_type
12273697456307, 1, "return"
12273697489075, 1, "return"
e
ok, I get it, but then I will have to loop over them to build and array?
a
yup
easy though yeah?
e
sorry, but how is it easier? isn't it all about build it where you're more confortable?
a
yeah but you're building a string, and you want an array.
And "where you're more confortable" doesn't trump "but it needs to work"
Also mishmashing where you are doing yer logic is less than ideal (ie: some in the DB... undoing what the DB did in your CFML... do more CFML... etc)
Copy code
<cfset line_items_array = []>
<cfloop query="q0">
    <cfset line_items_array.append({
        "line_item_id" = line_item_id,
        "quantity" = quantity,
        "restock_type" = restock_type
    })>
</cfloop>
Leave the DB to do storage logic. That's what it's for. Leave CFML to do the request/response logic. That's what it's for.
m
⭐ 1
e
I really appreciate your help educating me along the way. Not trying to argue here, but: Database is not for just storing the data. Perhaps many years ago it was. I just don't see how 1. running a database query 2. switch to CF 3. loop over the result set to populate an array 4. return it is better than 1. running a database query 2. return result ( 0.015s)
m
are you building json from your database?
a
Because yer DB result is a string and you want a frickin array. OMG
🤣 1
And... you are splitting the same basic task over two separate platforms. It's just a shit way of doing things.
If yer DB could do the whole lot (create the entire response, as JSON) then I could possibly see a case for doing it all on the DB. It's still a crap separation of concerns though. Plus even if I was to do it in the DB, I still would not be building partial JSON strings by hand with 
LISTAGG
. I'd still use an internal DB-specific data structure, and then at the exit point convert it to JSON.
Right. After midnight here & "not CFML" is calling me. I will eyeball where you get to tomorrow.
e
@epipko like others already pointed out there are more efficient ways of doing what you need. • don't build JSON inside your query • always use 
cfqueryparam
for sql arguments • build your returned array or structure first • then use the 
serializeJSON()
to turn that to a string You never mentioned where the flag 
complete_refund
is coming from. It's not good REST practice to optionally include elements in your JSON. In your example, you cannot sometimes return a 
full_refund
or 
amount
keys and sometimes not. You always want to include them, just have it say 
"full_refund": true
or 
"full_refund": false
depending on the case. I realize you might be starting out on CF, so tags it is. However, I would strongly advice you to use CFSCRIPT everywhere in your logic. Use tags only with CFM files and mixing it with HTML. With that said, I rewrote your code using tags (yikes!) in a gist. In my example I am mocking the query but you would use the commented out one on top. Then move your 
complete_refund
as needed. https://trycf.com/gist/69a4701d8689f916e9abfb3d47e9896e/acf2021?theme=xcode
⭐ 1
e
Wow, is this officially the longest thread? It was not my intention to raise your blood pressure by asking, what looked to me, a simple question. I appreciate all of your help. in the end, as Adam mentioned, it has to work, and it does. I learned alternative (correct?) ways of doing what am trying to to achieve my goal. Adam challenged me and I just might build the entire JSON from database. I will pay attention to # signs and try use <cfqueryparam> in all my queries from now on. Appreciate everyone's help and I hope you can help me in the future.
👍 1
m
`...and I just might build the entire JSON from database`: this is the opposite of what Adam said to do...
e
If yer DB could do the whole lot (create the entire response, as JSON) then I could possibly see a case for doing it all on the DB.
m
but again, you'd be building the string by hand, which is prone to errors.
e
Databases are different now
Copy code
SELECT json_object('name'          VALUE first_name || ' ' || last_name,
                   'hasCommission' VALUE
                     CASE WHEN commission_pct IS NULL THEN 'false' ELSE 'true'
                     END FORMAT JSON)
  FROM employees WHERE first_name LIKE 'W%';

JSON_OBJECT('NAME'ISFIRST_NAME||''||LAST_NAME,'
-----------------------------------------------
{"name":"William Gietz","hasCommission":false}
{"name":"William Smith","hasCommission":true}
{"name":"Winston Taylor","hasCommission":false}
m
ok
e
just an example
e
@epipko experimenting and interacting is how we learn, so don't worry about asking. Along the lines of the DB creating JSON, in this specific case which you are working on it looks like you are better off not doing that and using CF for the logic and a simple 
cfquery
for the values.
@epipko for comparison, here's my previous code in CFSCRIPT. Make sure to add 
var
in front of all your variables when declaring them inside a function to isolate them in scope within the function only. https://trycf.com/gist/fd754f3eea7a8d8f2cc9bfbde527c051/acf2021?theme=xcode
e
Thank you Evagoras, but <cfscript> is way over my head right now.
👍 1
Open in Slack
PreviousNext
Powered by