logo
down
shadow

SQL query to get conflicting values in JSONB from a group


SQL query to get conflicting values in JSONB from a group

By : wildewutz
Date : October 16 2020, 08:10 PM
it should still fix some issue There are at least two solid approaches, depending on how complex you want to get and how numerous and/or dynamic the keys are. The first is very straightforward:
code :
select location_id
from report
where report_type = 'R'
group by location_id
having count(distinct data->'key1') > 1 
  or count(distinct data->'key2') > 1
  or count(distinct data->'key3') > 1
--note that we also need distinct on location id to return one row per location
select distinct on(location_id) location_id
 --jsonb_each returns the key, value pairs with value in type JSON (or JSONB) so the value field can handle integers, text, arrays, etc 
from report, jsonb_each(data)
where report_type = 'R'
 and key in('key1', 'key2', 'key3')
group by location_id, key
having count(distinct value) > 1
order by location_id


Share : facebook icon twitter icon
Postgres JSONB: query values in JSON array

Postgres JSONB: query values in JSON array


By : user4196633
Date : March 29 2020, 07:55 AM
I think the issue was by ths following , Postgres documentation regarding json is really great. As for search query approach it's important to know that ->> returns text and -> returns json(b).
Query can be the following:
code :
select * from json js,jsonb_array_elements(data->'arrayAttribute') as array_element  
where (js.data->>'attributeA')::integer = 1 
and js.data->>'attributeB' = 'Foo' 
and (array_element->>'attributeC')::integer >= (100-5) 
and (array_element->>'attributeC')::integer <= (100+5);
SELECT * FROM json js,jsonb_extract_path(data,'arrayAttribute') AS entireArray 
WHERE (entireArray -> 0 ->> 'attributeC')::integer = 95
AND (entireArray -> 1 ->> 'attributeC')::integer = 105;
How to query an array of values within a JSONB field dictionary?

How to query an array of values within a JSONB field dictionary?


By : jm7296
Date : March 29 2020, 07:55 AM
I wish did fix the issue. I'm not sure if there's any better way -- that honestly looks pretty straightforward to me.
As for indexing, there are a couple things you can do.
code :
CREATE INDEX ON things ( my_column -> 'a' );
CREATE INDEX ON things (my_column)
  WHERE my_column -> 'a' IS NULL OR NOT
    my_column -> 'a' ? 'X';
Rails PostgreSQL jsonb GROUP BY query on internal dynamic keys

Rails PostgreSQL jsonb GROUP BY query on internal dynamic keys


By : Pretty5225
Date : March 29 2020, 07:55 AM
To fix the issue you can do As a matter of fact... you are able to make it work! Do the following query:
code :
query = <<-SQL 
  select errors.types, COUNT(errors.types)
  from (select jsonb_object_keys(error_messages) as types from api_responses) errors
  group by errors.types
SQL

your_result = API::Response.find_by_sql(query)
Query for array values within jsonb column

Query for array values within jsonb column


By : user3426831
Date : March 29 2020, 07:55 AM
To fix this issue I have a jsonb column called chores in my work table with data looking something like this: , You need to unnest the array:
code :
select w.*
from "work" w
where exists (select *
              from jsonb_array_elements(w.chores) as t(task)
              where t.task ->> 'task" = 'feed_dog');
select *
from "work" w
where jsonb_path_exists(w.chores, '$[*] ? (@.task == "feed_dog")')
How to query the values in a jsonb column in PostgreSQL?

How to query the values in a jsonb column in PostgreSQL?


By : user3542303
Date : March 29 2020, 07:55 AM
it helps some times Given a jsonb column called pairs with data such as the following in a single record: , In Postgres 9.5 use the function jsonb_each_text() in a lateral join:
code :
with my_table(pairs) as (
values 
    ('{ "foo": 1, "bar": 2 }'::jsonb)
)

select t.*
from my_table t
cross join jsonb_each_text(pairs)
where value = '1';
select *
from my_table
where jsonb_path_exists(pairs, '$.* ? (@ == 1)')
Related Posts Related Posts :
  • SQL Query - Group consecutive items based on condition
  • Users who work in same department
  • Syntax error near column value Vb
  • Oracle Trigger BEFORE INSERT has No data found
  • What kind of join to use on SQL tables
  • Is there a way to add a constant value dynamically to all records returned in Hive?
  • SQL optimization (inner join or selects)
  • EF 6.x, LINQ-to-SQL and raw SQL clauses
  • Simple SQL Variable Assignment Only Returns One Letter: Why?
  • Converting a custom timestamp to date
  • SQL Server : inserting Player vs Player names in to new table from tblEntrants
  • invalid identifier in sql
  • PL/SQL - I keep getting this error when concatenating: PLS-00306: wrong number or types of arguments in call to '||'
  • Count records only from left side of a LEFT JOIN
  • get everything before a string including itself oracle
  • Format Data from Word Doc to SQL using RegEX
  • Conditional formatting on MAX value row
  • MS-Access : selecting data from two tables and only returning you need
  • SQL Server: optimal indexing strategies for many-to-many join
  • DBgrid column very wide
  • PostgreSQL Group values by category, count and calculate percentage
  • MS Access SQL - Most Recent Record for Each Consultant ID
  • Update table: Summary of previous rows without using cursor or while loop
  • PostgreSQL: built-in function to remove substring starting with certain pattern
  • ORA-00909: invalid number of arguments
  • How to summarize all possible combinations of variables?
  • Select Column within a Column SQL
  • PostgreSQL Inserting 2 relationships at once
  • T sql - How to store results from a dynamic query using EXEC or EXECUTE sp_executesql
  • How do I parse my json into CSV using regex?
  • Reverse foreign key cascading (or how to collect database garbage)
  • SQL Pivot Questions
  • Insert records into a table with a condition in SQL Server 2016
  • display null value using rank functions in oracle sql
  • SQL - Get count of group by column but also select top item of group
  • How to add an array of datarows into an exisitng table inside my database
  • There is no unique constraint matching given keys for referenced table "employee" 1
  • SQL: Unable to SELECT joined column
  • How to find out how much space a SQL Server table uses?
  • Window function to remove specific records from SQL Server dataset
  • How to add a column for each day in sql?
  • Create group column based on the specific rows
  • Not sure if this consistitues a transitive dependency
  • How to compare the values in a column to a long list in SQL Server
  • Preserving data format Decimal(6,5) from vba to sql
  • Oracle Query to rollup QTY by Year- only last 3 years
  • SQL - Calculate 2 columns and view result to another to column
  • Divide or Multiply according to a condition (Improving query)
  • PostgreSQL unnest() with consecutive integers grouped by number
  • SQL to limit output to certain months and years
  • VARCHAR TIME TO GET THE DIFFERENCE
  • SQL conditional constraint on multiple columns being unique
  • Optimize a SQL select query in a loop
  • BTEQ Teradata Import Multiple files into one table
  • Update SQL datetime column with oldest values of another table column?
  • Is INSERT ... SELECT an atomic transaction?
  • SQL query completed successfully but not results
  • SQL sub select returning multiple values
  • Verify condition on two columns
  • SQL conditional field, first match JOIN
  • shadow
    Privacy Policy - Terms - Contact Us © voile276.org