Select value from object(dynamic) within object(dynamic)

CrateDB SQL
1

Hello! I have the following table (obtained through the “SHOW CREATE TABLE” SQL directive):

"doc"."etgeometry_msgs%2ftwist" (
   "entity_id" TEXT,
   "entity_type" TEXT,
   "time_index" TIMESTAMP WITH TIME ZONE,
   "fiware_servicepath" TEXT,
   "__original_ngsi_entity__" OBJECT(DYNAMIC),
   "angular" OBJECT(DYNAMIC) AS (
      "x" OBJECT(DYNAMIC) AS (
         "type" TEXT,
         "value" BIGINT
      ),
      "y" OBJECT(DYNAMIC) AS (
         "type" TEXT,
         "value" BIGINT
      ),
      "z" OBJECT(DYNAMIC) AS (
         "type" TEXT,
         "value" BIGINT
      )
   ),
   "linear" OBJECT(DYNAMIC) AS (
      "x" OBJECT(DYNAMIC) AS (
         "type" TEXT,
         "value" BIGINT
      ),
      "y" OBJECT(DYNAMIC) AS (
         "type" TEXT,
         "value" BIGINT
      ),
      "z" OBJECT(DYNAMIC) AS (
         "type" TEXT,
         "value" BIGINT
      )
   )
)

How could I get angular.x.value using “SELECT” directive from SQL?

I’m trying this query:

SELECT
  time_index AS "time",
  linear.x.value AS metric,
  linear.x.value
FROM "doc"."etgeometry_msgs%2ftwist"
WHERE
  $__timeFilter(time_index)
ORDER BY 1,2

But it does not work

2

Hi @rafluq

Welcome to the CrateDB Community!

You have to use the bracket syntax to select on object properties. ie

SELECT
  time_index AS "time",
  linear['x']['value'] AS metric,
  linear['x']['value']
FROM "doc"."etgeometry_msgs%2ftwist"
WHERE
  $__timeFilter(time_index)
ORDER BY 1,2

also see Selecting data — CrateDB: Reference

2 Likes
3

Thank you very much for your answer!

1 Like