Can i have whole record as a json in select query and having condition on referenced table as well?

[ashvin-kumbhani]

ArcadeDB Version:

24.1.1

OS and JDK Version:

Linux 6.5.0-1014-gcp - OpenJDK 64-Bit Server VM 11.0.22 (Temurin-11.0.22+7)

Expected behavior

To have all the properties of linked record in JSON format and to filter multiple linked records with where clause on one property of linked bucket.

Actual behavior

When we try to filter linked records with dot operator from LIST property, it simply doesn’t work without giving any error.

SQL query to produce with schema

• type: document
• bucket 1: Users
• bucket 2: Locations
• Users and Locations buckets are linked by storing all @rids of Locations records in the Users property called hasLocations. example: hasLocations = [# 1:10, # 2:20] (I put the space intentionally in between # and number)
• After storing rids, I can easily fetch single properties like city, and country from the Locations bucket with the dot operator.

query : select hasLocations.city from Users

*which gives all the cities associated with the linked location record. but I want to fetch all properties of Locations and I can not find any method for that.

• And if I want to put any condition on Location property like fetch only default locations then it simply doesn’t work without error.
  query: select hasLoctions.city from Users where hasLocation.is_default = true.

Steps to reproduce

• have two buckets of document types
• link them by storing @rids of one into LIST property of another bucket
• try to put condition on that LIST property in select statement

[gramian]

Hi,
with respect to the query: did you already try something like

SELECT hasLocation.transform('asRecord') FROM Users

I haven't tested this, but it should apply the asRecord converter method to each element in the hasLocations property and thus return a list of documents.

[ashvin-kumbhani]

Hi, with respect to the query: did you already try something like

SELECT hasLocation.transform('asRecord') FROM Users

I haven't tested this, but it should apply the asRecord converter method to each element in the hasLocations property and thus return a list of documents.

this can work, however is there any way to have conditions on where clause for example
SELECT hasLocation.transform('asRecord') FROM Users WHERE hasLocations.is_default = true

[gramian]

To be able to help with the filter you need to provide the error and stacktrace.

[ashvin-kumbhani]

SELECT hasLocation.transform('asRecord') FROM Users WHERE hasLocations.is_default = true

I am not getting any errors, receiving simply null records. The reason query is not able to filter because of hasLocation is a list and hasLocation.is_default will not get any value for filter. So I wanted to know about any other way for filtering linked records

[ashvin-kumbhani]

also it will be helpful to get preferred properties in hasLocation.transform('asRecord') instead of all including @Rid, type etc

[lvca]

You can flatten the list in a sub query and then apply the filters you like:

SELECT FROM (
  SELECT expand( hasLocation ) FROM Users
) WHERE is_default = true