I have a column (student_no) in my database with JSON data (String). student No: [ {First_name: 'John', Last_name: 'Wick', student_id: 1001, }, {First_name: 'David', Last_name: 'Stone', student_id: 1002, } If I want to read and display only the student_id =…
I have a table in Postgres with the structure bellow. id name objects(jsonb) 1 first [{"name":"a", value:"1", "param":{}}, {"name":"b", "value":"55", "param":{}}, {"name":"c", "value":"234", "param":{}}] 2 second [{"name":"b", "value":"22", "param":{}}, {"name":"c", "value":"24", "param":{}}] 3 third [{"name":"c", "value":"345", "param":{}}] | 4 forth…
How are you doing? I'm currently using PostgreSQL and want to create a 'where' with just a specif date but the value returns me a kind of timestamp. Example of the json: What the json looks like: {"key1": {"key2": "2023-02-13T13:28:49.087000Z"}}…
I would like to have a variable in my query. CREATE TEMP TABLE tmp_date_var AS SELECT '2023-02-21'::DATE AS TargetDate; and then I used it in my (long) query where TargetDate appears quite often. The first run was successful, then I…
I have table like follows. id is sequential number and they order ascending. id score name 1 10 A 2 20 B 3 30 C 4 40 D I would like to calculate subtotal by using specific variable for example…
Below is my JSON and query I've built so far, I feel like I'm just missing some silly piece. These data files are loaded as-is into SQL Server tables, so this JSON exists in one column. Ultimately the nested array…
I have a table similar to the following. (The addresses are actually IP addresses, but I'm keeping them short for simplicity.) | Cust ID | Address | | ------- | --------| | 1 | 111 | | 1 | 222…
I'm trying to order a list of addresses by street name, then street number. Both forms of this data reside in the same column, 'address' within my schema, 'data'. The data have a similar format to: 27 Longshire Cir 109…
Can someone help me understand what this SQL query is doing? select region_code, ST_Area(geom::geography) as area, ST_X(ST_Centroid(geom)) as long, ST_Y(ST_Centroid(geom)) as lat, latitude, longitude from regions where regexp_split_to_array(parent, E'\\.') && %s or region_code in %s More specifically I'm confused about…
INSERT INTO "public"."catalog_element" ("id", "catalogue_id", "element_id", "weight") VALUES (1,100,1,0), (2,100,2,1), (3,100,3,2), (4,10,1,0), (5,10,5,0), (6,10,6,1), (7,100,7,1); Let say, we have a Postgres table of catalog *- to -* element matching rows that are weighted, like such id catalogue_id element_id weight 1…