Select from a Table with Array Columns

The following code snippets will showcase how you can leverage the array support to retrieve and manipulate data.

Querying Arrays Values
Query Arrays Using PreparedStatement

This example shows how you would bind an array value using a Prepared Statement, making it easier to parameterize your queries.

try:
cursor = conn.cursor()
rs = con_raw.cursor().execute("SELECT id FROM tarry WHERE doubles = ?", (LXARRAY(0.023533916958532797, 5.476721734666199, 1.0], float)))

for row in rs:
            print('id ' + row[0])

except Exception as e:
     print("Error:", e)
finally:
    cursor.close()
Recovery of the Array Values as String

You can retrieve the values of arrays as strings, which can be particularly useful if you need to process or display them in a human-readable format.

try:
sqlSelect = "SELECT id, TO_string(arryBigInt), TO_string(arryFloat), TO_string(arryVARCHAR) FROM tarry where id=1"

	rs = con.execute(sqlSelect)

	for row in rs:
            nrows +=1
            print(row)

except Exception as e:
     print("Error:", e)
finally:
    cursor.close()
Filter Using the Array Column
Filter Using the Entire Column

You can filter all the records with an array column containing a specific array by constructing SQL queries that match the desired array values.

try:
sqlSelect = "SELECT id, TO_string(arryBigInt), TO_string(arryFloat), TO_string(arryVARCHAR) FROM T_ARRAY where id=1"

	rs = con.execute(sqlSelect)

	for row in rs:
            nrows +=1
            print(row)

except Exception as e:
     print("Error:", e)
finally:
    cursor.close()
Filter all the Records with an Array Column Containing a Specific Array
try:
sqlSelect = "SELECT id FROM tarry, UNNEST(tarry.strings) AS t(flattened_string) where flattened_string='MADRID]"

	rs = con.execute(sqlSelect)

	for row in rs:
            nrows +=1
            print(row)

except Exception as e:
     print("Error:", e)
finally:
    cursor.close()
Filter by the Array Column Position

To filter records based on the value of a position of an array within a column, you can utilize LeanXcale’s features to retrieve them.

try:
sqlSelect = " SELECT id FROM tarry WHERE longs[3] = 5575876413993699465""

	rs = con.execute(sqlSelect)

	for row in rs:
            nrows +=1
            print(row)

except Exception as e:
     print("Error:", e)
finally:
    cursor.close()

These capabilities empower you to work effectively with array columns in LeanXcale, enabling you to handle complex data structures and perform operations straightforwardly.