SoftTree Technologies SoftTree Technologies
Technical Support Forums
RegisterSearchFAQMemberlistUsergroupsLog in
[SA 9.1.276 Pro] - Formatting MySQL having JSON column

 
Reply to topic    SoftTree Technologies Forum Index » SQL Assistant View previous topic
View next topic
[SA 9.1.276 Pro] - Formatting MySQL having JSON column
Author Message
gemisigo



Joined: 11 Mar 2010
Posts: 1398

Post [SA 9.1.276 Pro] - Formatting MySQL having JSON column Reply with quote
Formatting code in MySQL that includes parts retrieving data from JSON columns will result in broken code. This:
Code:

SELECT
    p.id
    ,p.attributes -> "$.priority"       AS priority
    ,p.attributes -> "$.sat"            AS sat
    ,p.attributes -> "$.event"          AS EVENT
    ,p.attributes -> "$.di1"            AS di1
    ,p.attributes -> "$.di2"            AS di2
    ,p.attributes -> "$.di3"            AS di3
    ,p.attributes -> "$.di4"            AS di4
    ,p.attributes -> "$.io71"           AS io71
    ,p.attributes -> "$.io240"          AS io240
    ,p.attributes -> "$.io21"           AS io21
    ,p.attributes -> "$.io239"          AS io239
    ,p.attributes -> "$.io208"          AS io208
    ,p.attributes -> "$.io209"          AS io209
    ,p.attributes -> "$.adc1"           AS adc1
    ,p.attributes -> "$.io10"           AS io10
    ,p.attributes -> "$.battery"        AS battery
    ,p.attributes -> "$.io68"           AS io68
    ,p.attributes -> "$.io181"          AS io181
    ,p.attributes -> "$.hdop"           AS hdop
    ,p.attributes -> "$.power"          AS POWER
    ,p.attributes -> "$.io24"           AS io24
    ,p.attributes -> "$.deviceTemp"     AS deviceTemp
    ,p.attributes -> "$.io199"          AS io199
    ,p.attributes -> "$.io241"          AS io241
    ,p.attributes -> "$.io76"           AS io76
    ,p.attributes -> "$.io205"          AS io205
    ,p.attributes -> "$.ip"             AS ip
    ,p.attributes -> "$.distance"       AS distance
    ,p.attributes -> "$.totalDistance"  AS totalDistance
FROM
    positions                            AS p
ORDER BY
    p.id

is going to end up as:
Code:

SELECT
    p.id
    ,p.attributes - > "$.priority"       AS priority
    ,p.attributes - > "$.sat"            AS sat
    ,p.attributes - > "$.event"          AS EVENT
    ,p.attributes - > "$.di1"            AS di1
    ,p.attributes - > "$.di2"            AS di2
    ,p.attributes - > "$.di3"            AS di3
    ,p.attributes - > "$.di4"            AS di4
    ,p.attributes - > "$.io71"           AS io71
    ,p.attributes - > "$.io240"          AS io240
    ,p.attributes - > "$.io21"           AS io21
    ,p.attributes - > "$.io239"          AS io239
    ,p.attributes - > "$.io208"          AS io208
    ,p.attributes - > "$.io209"          AS io209
    ,p.attributes - > "$.adc1"           AS adc1
    ,p.attributes - > "$.io10"           AS io10
    ,p.attributes - > "$.battery"        AS battery
    ,p.attributes - > "$.io68"           AS io68
    ,p.attributes - > "$.io181"          AS io181
    ,p.attributes - > "$.hdop"           AS hdop
    ,p.attributes - > "$.power"          AS POWER
    ,p.attributes - > "$.io24"           AS io24
    ,p.attributes - > "$.deviceTemp"     AS deviceTemp
    ,p.attributes - > "$.io199"          AS io199
    ,p.attributes - > "$.io241"          AS io241
    ,p.attributes - > "$.io76"           AS io76
    ,p.attributes - > "$.io205"          AS io205
    ,p.attributes - > "$.ip"             AS ip
    ,p.attributes - > "$.distance"       AS distance
    ,p.attributes - > "$.totalDistance"  AS totalDistance
FROM
    positions                            AS p
ORDER BY
    p.id

in which all the '->''s are separated into '- >' which has a surplus space between '-' and '>', thus turning the code into a syntax error.
Thu Jul 06, 2017 8:17 am View user's profile Send private message
SysOp
Site Admin


Joined: 26 Nov 2006
Posts: 6507

Post Reply with quote
May I suggest adding a custom formatting style that doesn't add spaces around operators and using it in such cases?
Thu Jul 06, 2017 11:43 am View user's profile Send private message
gemisigo



Joined: 11 Mar 2010
Posts: 1398

Post Reply with quote
Yes, I was using that as a workaround for some time. Do you think you're going to fix this one? I guess it's not just as simple as adding '->' to the list of operators but being forced to have different formatting rules applied to different pieces of code in multiple steps is error prone and is going to get quite inconvenient in the long run.
Fri Jul 07, 2017 3:01 am View user's profile Send private message
Display posts from previous:    
Reply to topic    SoftTree Technologies Forum Index » SQL Assistant All times are GMT - 4 Hours
Page 1 of 1

 
Jump to: 
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum


 

 

Powered by phpBB © 2001, 2005 phpBB Group
Design by Freestyle XL / Flowers Online.