Search Results
| Search type | Search syntax |
|---|---|
| Tags | [tag] |
| Exact | "words here" |
| Author |
user:1234 user:me (yours) |
| Score |
score:3 (3+) score:0 (none) |
| Answers |
answers:3 (3+) answers:0 (none) isaccepted:yes hasaccepted:no inquestion:1234 |
| Views | views:250 |
| Code | code:"if (foo != bar)" |
| Sections |
title:apples body:"apples oranges" |
| URL | url:"*.example.com" |
| Saves | in:saves |
| Status |
closed:yes duplicate:no migrated:no wiki:no |
| Types |
is:question is:answer |
| Exclude |
-[tag] -apples |
| For more details on advanced search visit our help page | |
Results tagged with json
Search options answers only
not deleted
user 112361
JavaScript Object Notation (JSON) is an open, human and machine-readable standard that facilitates data interchange, and along with XML is the main format for data interchange used on the modern web.
1
vote
Accepted
Postgresql and jsonb: perform a like over all values of an array
You can use the following query
SELECT
id_user
FROM
users
JOIN LATERAL jsonb_array_elements(emails) AS x(email) ON true
WHERE
email::text like '%email%' ;
jsonb_array_elements() r …
- 13.6k
11
votes
Accepted
Getting the count and sum of distinct record from Postgres jsonb attribute
(b), returning a json(b) object
jsonb ->> field => gets the field out of the json(b), returning it as text
And you will just get:
Sector | Count | Total | Male | Female
:--------- | ----: | --- … You can store JSON as text, and you can already use any database, even if it is not aware of JSON. …
- 13.6k
3
votes
Accepted
postgresql count null and blank value from jsonb column
) -- Note the ::json to make sure PostgreSQL uses the proper type
)
, second_elements AS
(
SELECT
json_array_elements(var)->1 AS e
FROM
original_data
)
SELECT
count(e) AS total_elements, … NOTE: If you want to use jsonb instead of json, just change the ::jsonwith ::jsonb in the first statement, and call the jsonb_array_elements instead of json_array_elements. …
- 13.6k
4
votes
Accepted
possible to reference a jsonb_array_length in a where clause
This is the standard behaviour of SQL. SQL statements are (logically) processed in a certain specified order.
Following that order, the WHERE clause is executed before the SELECT one. That's why the …
- 13.6k
4
votes
How can I use a full-text search on a jsonb column with Postgres?
You can, although whether that's practical is not so clear:
CREATE TABLE t
(
id SERIAL PRIMARY KEY,
the_data jsonb
) ;
CREATE INDEX idx_t_the_data_full_text
ON t
USING gist ( (to_t …
- 13.6k
1
vote
Accepted
Order by exact matches (jsonb array), then lexeme similarity
Check if this is what you're looking for [I didn't fully understand your first OR / AND condition. I assumed it was just an OR].
WITH posts AS
(
SELECT
*
FROM
(VALUES
('how do I say …
- 13.6k
40
votes
Accepted
Creating a UNIQUE constraint from a JSON object
JSON is not for that. … You would index then a JSON(B) expression instead of a text. Check JSON Functions and Operators. …
- 13.6k
0
votes
Accepted
How to manipulate bytea data when using backup script as a template
Instead of using...
COPY example_table (id, ip_address, domain, configuration) FROM stdin;
1 0.0.0.0 invalid.domain.com \x7b277265666572656e63655f746f5f69705f61646472657373273a27302e302e302e30272c …
- 13.6k
1
vote
Accepted
PostgreSQL function's input/output as JavaScript nested objects
JavaScript nested objects can be easily (and I'd say "naturally") represented using JSON. This is "by definition" of what JSON (JavaScript Object Notation) is. … See also Safely turning a JSON string into an object.
In PostgreSQL, on top of using json data type, you might also use jsonb. …
- 13.6k
5
votes
Accepted
Join JSONB column to normal PK column in Postgres
Things to take into consideration:
The ->> operator gets a JSON object field as text. … On the JSON(B) side, you need to represent the UUIDs using the standard form (as explained in UUID Type), so that conversions between UUID type to the JSON(B) text representation produces the same results …
- 13.6k
0
votes
Is there a faster way to count JSONB tags?
Apart from @Dudu Markowitz answer, which I think is correct, if you are limited in how you can restructure your query, you can, at least do something like:
SELECT sum(tag1) AS sum_tag1, sum(tag2) AS …
- 13.6k
0
votes
Accepted
Nested json response from 3 one to many tables
In PostgreSQL:
SELECT
row_to_json (r1.*)
FROM
(
SELECT
table_1_id,
somedata,
array(
SELECT
row_to_json (r2.*) …
- 13.6k