Greetings
Spent a few hours looking at docs but not getting what I need to extract specific data from nested jsonlike data
This is my data in an OBJECT date type column
{
"actionType": "append",
"entities": [
{
"name": {
"type": "Text",
"value": "Corner Unit"
},
"maxCapacity": {
"type": "Integer",
"value": 50
},
"location": {
"type": "geo:json",
"value": {
"coordinates": [
13.3986112,
52.554699
],
"type": "Point"
}
},
"id": "urn:ngsi-ld:Shelf:unit001",
"type": "Shelf",
"refStore": {
"type": "Relationship",
"value": "urn:ngsi-ld:Store:001"
}
},
{
"name": {
"type": "Text",
"value": "Wall Unit 1"
},
"maxCapacity": {
"type": "Integer",
"value": 100
},
"location": {
"type": "geo:json",
"value": {
"coordinates": [
13.3987221,
52.554664
],
"type": "Point"
}
},
"id": "urn:ngsi-ld:Shelf:unit002",
"type": "Shelf",
"refStore": {
"type": "Relationship",
"value": "urn:ngsi-ld:Store:001"
}
},
{
"name": {
"type": "Text",
"value": "Wall Unit 2"
},
"maxCapacity": {
"type": "Integer",
"value": 100
},
"location": {
"type": "geo:json",
"value": {
"coordinates": [
13.3987221,
52.554664
],
"type": "Point"
}
},
"id": "urn:ngsi-ld:Shelf:unit003",
"type": "Shelf",
"refStore": {
"type": "Relationship",
"value": "urn:ngsi-ld:Store:001"
}
},
{
"name": {
"type": "Text",
"value": "Corner Unit"
},
"maxCapacity": {
"type": "Integer",
"value": 50
},
"location": {
"type": "geo:json",
"value": {
"coordinates": [
13.390311,
52.507522
],
"type": "Point"
}
},
"id": "urn:ngsi-ld:Shelf:unit004",
"type": "Shelf",
"refStore": {
"type": "Relationship",
"value": "urn:ngsi-ld:Store:002"
}
},
{
"name": {
"type": "Text",
"value": "Long Wall Unit"
},
"maxCapacity": {
"type": "Integer",
"value": 200
},
"location": {
"type": "geo:json",
"value": {
"coordinates": [
13.390309,
52.50751
],
"type": "Point"
}
},
"id": "urn:ngsi-ld:Shelf:unit005",
"type": "Shelf",
"refStore": {
"type": "Relationship",
"value": "urn:ngsi-ld:Store:002"
}
},
{
"name": {
"type": "Text",
"value": "Corner Unit"
},
"maxCapacity": {
"type": "Integer",
"value": 50
},
"location": {
"type": "geo:json",
"value": {
"coordinates": [
13.4447112,
52.503199
],
"type": "Point"
}
},
"id": "urn:ngsi-ld:Shelf:unit006",
"type": "Shelf",
"refStore": {
"type": "Relationship",
"value": "urn:ngsi-ld:Store:003"
}
},
{
"name": {
"type": "Text",
"value": "Wall Unit 1"
},
"maxCapacity": {
"type": "Integer",
"value": 100
},
"location": {
"type": "geo:json",
"value": {
"coordinates": [
13.4447221,
52.503164
],
"type": "Point"
}
},
"id": "urn:ngsi-ld:Shelf:unit007",
"type": "Shelf",
"refStore": {
"type": "Relationship",
"value": "urn:ngsi-ld:Store:003"
}
},
{
"name": {
"type": "Text",
"value": "Wall Unit 2"
},
"maxCapacity": {
"type": "Integer",
"value": 100
},
"location": {
"type": "geo:json",
"value": {
"coordinates": [
13.4447221,
52.503164
],
"type": "Point"
}
},
"id": "urn:ngsi-ld:Shelf:unit008",
"type": "Shelf",
"refStore": {
"type": "Relationship",
"value": "urn:ngsi-ld:Store:003"
}
},
{
"name": {
"type": "Text",
"value": "Corner Unit"
},
"maxCapacity": {
"type": "Integer",
"value": 50
},
"location": {
"type": "geo:json",
"value": {
"coordinates": [
13.444711,
52.503122
],
"type": "Point"
}
},
"id": "urn:ngsi-ld:Shelf:unit009",
"type": "Shelf",
"refStore": {
"type": "Relationship",
"value": "urn:ngsi-ld:Store:003"
}
},
{
"name": {
"type": "Text",
"value": "Long Wall Unit"
},
"maxCapacity": {
"type": "Integer",
"value": 200
},
"location": {
"type": "geo:json",
"value": {
"coordinates": [
13.4094111,
52.5208028
],
"type": "Point"
}
},
"id": "urn:ngsi-ld:Shelf:unit010",
"type": "Shelf",
"refStore": {
"type": "Relationship",
"value": "urn:ngsi-ld:Store:004"
}
}
]
}
The following is my query but I am not getting what I need
SELECT store_details,
store_details['entities']['name']['value'] nm,
store_details['entities']['location']['value']['coordinates'] coordinates
FROM doc.berliner
Could you please point me to doc so that I get a final result that looks like this?
name,lon,lat
Bösebrücke Einkauf,13.3986,52.5547
Checkpoint Markt,13.3903,52.5075
East Side Galleria,13.4447,52.5031
Tower Trödelmarkt,13.4094,52.5208
