2

I've been at this for a few hours and can't figure it out. So I thought maybe someone here would have an idea.

I have JSON which is being published to a thing shadow(format of JSON being posted is not under my control). The JSON looks like this:

{"state":{"reported":{"0013A20011223344":{"input_1":1}}}}

I tried this query statement which would be used to send a publish to SNS:

SELECT "input closed" AS default FROM "$aws/things/Cell_Test_Thing/shadow/update" WHERE state.reported.0013A200418C5535.input_1 = 1

The problem is the SQL query does not like that key starting with a 0. I determined this by replacing 0013... with device here and it worked perfectly:

SELECT "input closed" AS default FROM "$aws/things/Cell_Test_Thing/shadow/update" WHERE state.reported.device.input_1 = 1

So the problem is that JSON key beginning with a number. If I change device to device0 it works but if you change it to 0device it fails. From what I can determine it is perfectly valid to start a JSON key with a number so hopefully there is a work around for this.

Ideas?

Travis Elliott
  • 291
  • 2
  • 5
  • 18
  • 1
    Have you tried surrounding the "columns" with quotes yet? Other SQL implementations [allow for that](https://stackoverflow.com/questions/7975417/can-a-number-be-used-to-name-a-mysql-table-column). – Ellesedil Jul 18 '19 at 17:38
  • Thanks for your input @Ellesedil. I tried encapsulating the ID with ticks like this but got the error unexpected character '`' SELECT "Input Closed" AS default FROM "$aws/things/Cell_Test_Thing/shadow/update" WHERE state.reported.`0013A200418C5535`.input_1 = 1 – Travis Elliott Jul 18 '19 at 18:21
  • Well, I'm pretty sure the answer is to surround the property path, or even individual parts of the path, with something. I doubt it should be back ticks, though. Try some stuff. – Ellesedil Jul 18 '19 at 18:27
  • @TravisElliott Were you able to find a solution to this problem? – himanish.k Aug 14 '20 at 16:35

2 Answers2

1

You can use the built-in get() method. Although it is not easy on the eye, it works.

SELECT "input closed" AS default 
FROM "$aws/things/Cell_Test_Thing/shadow/update" 
WHERE get(get(get(state, "reported"), "0013A200418C5535")), "input_1") = 1
mvltshn
  • 245
  • 3
  • 10
-1

Try this:

SELECT "input closed" AS default 
FROM "$aws/things/Cell_Test_Thing/shadow/update" 
WHERE "state"."reported"."0013A200418C5535"."input_1" = 1

Each part of the name must be in quotes. This is because the name has a structure in four parts.

Why can't you just use one set of quotes? Think about it this way. The four-part name "state"."reported"."0013A200418C5535"."input_1" has four parts, separated by dots. But what if one of those parts had a dot in it?

(Technically only parts which contain certain characters need to be quoted, but sometimes it's easier just to do them all).

Ben
  • 34,935
  • 6
  • 74
  • 113