score:2
Accepted answer
For Postgres you have two choices to make this query work properly:
select *
from the_table
where jsonb_array_length(json::jsonb -> 'thirdTypeLabels') = 0;
Or - starting with Postgres 12 - using a JSON Path expression
select *
from the_table
where jsonb_path_exists(json::jsonb, '$.thirdTypeLabels.size() ? (@ == 0)' );
Or use the same JSON path expression as in Oracle:
select *
from the_table
where jsonb_path_exists(json::jsonb, '$' ? (@.thirdTypeLabels.size() == 0)');
In Postgres you should also use a column defined as jsonb
rather than text
(or varchar
)
score:3
You should use a proper JSON parser otherwise there is no guarantee that %thirdTypeLabels%[]%
will restrict the match of the empty array to the thirdTypeLabels
key-value pair.
So for Oracle 18c you can use:
SELECT id,
thirdTypeLabelsCount
FROM mytable t
CROSS JOIN
JSON_TABLE(
t.json,
'$'
COLUMNS(
thirdTypeLabelsCount NUMBER PATH '$.thirdTypeLabels.size()'
)
)
WHERE thirdTypeLabelsCount = 0;
or
SELECT *
FROM mytable
WHERE JSON_EXISTS( json, '$ ? (@.thirdTypeLabels.size() == 0) ' )
More questions
- Escape square brackets in Oracle and PostgreSQL
- How to get an Object without wrapping of square brackets in PostgreSQL using Node.js and express?
- String literals and escape characters in postgresql
- Oracle SQL Developer and PostgreSQL
- Mysql vs Oracle XE vs Postgresql . Scalability and performance, which to chose?
- Write Skew anomaly in Oracle and PostgreSQL does not rollback transaction
- Hibernate with long text column with Oracle and PostgreSQL
- What is the most critical feature PostgreSQL lacks compared with Oracle and DB2 for OLTP?
- Is it possible to use a database link between an oracle database and a postgresql database on different physical servers
- how to make @Lob annotation compatible for both oracle and postgresql
- PostgreSQL - migrate a query with 'start with' and 'connect by' in oracle
- Different default error handling in Oracle and PostgreSQL
- Remove Square brackets in postgresql (SQL)
- How to select values as a list in PostgreSQL and Oracle Database?
- Convert Oracle stored procedure using REF_CURSOR and package global variable to Postgresql or MySQL
- ORDER siblings BY and rownum while migrating from Oracle to PostgreSQL
- Hibernate empty string for an equality restriction for PostgreSQL 8.3 and Oracle 11g
- PostgreSQL / Oracle => query to group and count hierarchical values
- Oracle m5 hash returns different string than do Postgresql and bash md5sum(1)
- Does PostgreSQL have any column which maintains uniqueness between table rows like as oracle has rowId and rowNum?
- Is PostgreSQL and Oracle Object-Relational storage the same as an ORM?
- Query to print date in yyyy-MM-dd'T'HH:mm:ss.SSSZ format in oracle and postgresql
- PostgreSQL array(row_to_json()): How to stop array() functions from adding " quotes to strings and escape \ existing " quotes
- How to use Oracle and PostgreSQL query in one select query
- PostgreSQL 11 and Oracle foreign data wrapper
- String Aggregation that works in both oracle and postgresql
- How to get a table-level check constraint in Oracle and PostgreSQL
- How would PostgreSQL and Oracle behave for a query like SELECT * WHERE id='1blah'?
- Delete .. from .. join, same syntax for postgresql and oracle
- How to know disabled/enabled indexes in PostgreSQL and Oracle
More questions with similar tag
- How to store multiple circles in a single PostGIS geometry column?
- Query optimization possibilities?
- Is this type of query safe from sql injection?
- How to declare local variables in postgresql?
- Can not install postgres in ubuntu
- Avoid repeated select statement when using "extra" clause in Django ORM
- Cannot SELECT reason for UNIQUE VIOLATION
- Heroku horizontal scaling
- Optimize SQL query with 3 FOR loops
- Query to disable triggers for a particular user in Postgres
- How to manually lock and unlock a row?
- sql database model for contact details
- Postgresql column reference is ambiguous
- Postgres. Inner join with 1 to many
- Missing max_parallel_degree configuration parameter in PostgreSql 9.6
- Automatically increasing the value of a variable in generate_series
- Select hardcoded values without table
- How to find (decode) PostgreSQL query from Wireshark File?
- How to delete an object in a json array element?
- Do voltDB or NuoDB need ElasticSearch?
- How can I set a default value for a column in pgAdmin4?
- Postgresql relation does not exist
- Sequelize schema for PostgreSQL: How to accurately define a schema in a model?
- POSTGRESQL INSERT if specific row name don't exists?
- How do I use named placeholders in a WITH statement (common table expression) with psycopg2?
- Setting up simple communication between a PostgreSL local database and a cloud database
- Case..When in Where Condition Spring boot data jpa
- Postgres trigger check amount before delete
- insert json data into postgres database using java program
- Reset increment in PostgreSQL
Source:
stackoverflow.com